Home » SQL & PL/SQL » SQL & PL/SQL » Procedure: insert else delete (merged)
Procedure: insert else delete (merged) [message #197668] Thu, 12 October 2006 04:26 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi

pls let me know how to create procedure

if order_no exists delete or insert order_no


thanxs
Re: create procedure for delete and insert [message #197671 is a reply to message #197668] Thu, 12 October 2006 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, that's my entry for the contest taken care of!
insert else delete proc pls edit [message #197688 is a reply to message #197668] Thu, 12 October 2006 05:46 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
create or replace PROCEDURE indel
(
DEL_ID number,ORD_no varchar2,INV_it varchar2,PACK_QUANT number,CONT_NAME varchar2,ORGA_ID number,OUT_CON_NAME varchar2,TRAC_NU varchar2,INV_ITEM_ID number,GR_WE number,WE_UO varchar2,CONT_DIM varchar2
)
as
begin
declare
DELIVERY_ID VARCHAR2(20);
ORDER_NUMBER VARCHAR2(20);
INVENTORY_ITEM VARCHAR2(50);
PACK_QUANTITY NUMBER;
CONTAINER_NAME VARCHAR2(30);
ORGANIZATION_ID NUMBER;
OUTERMOST_CONTAINER_NAME VARCHAR2(30);
TRACKING_NUMBER VARCHAR2(30);
INVENTORY_ITEM_ID NUMBER;
GROSS_WEIGHT NUMBER(3);
WEIGHT_UOM VARCHAR2(10);
CONTAINER_DIMENSIONS VARCHAR2(30);
begin
begin
BEGIN
select ORDER_NUMBER,INVENTORY_ITEM,PACK_QUANTITY,CONTAINER_NAME,ORGANIZATION_ID,OUTERMOST_CONTAINER_NAME,TRACKING_NUMBER,INVENTORY_ITEM_ID,G ROSS_WEIGHT,WEIGHT_UOM,CONTAINER_DIMENSIONS
into ORD_no,INV_it,PACK_QUANT,CONT_NAME,ORGA_ID,OUT_CON_NAME,TRAC_NU,INV_ITEM_ID,GR_WE,WE_UO,CONT_DIM from NAE_KEWILL_PACK_DETAILS
WHERE DELIVERY_ID=DEL_ID;
exception
when no_data_found then
dbms_output.put_line('del_id does not exists!!');
end;
DEL_ID:='DELIVERY_ID';
delete NAE_KEWILL_PACK_DETAILS
where DELIVERY_ID=DEL_ID
insert into NAE_KEWILL_PACK_DETAILS
values
(del_id,ORD_no,INV_it,PACK_QUANT,CONT_NAME,ORGA_ID,OUT_CON_NAME,TRAC_NU,INV_ITEM_ID,GR_WE,WE_UO,CONT_DIM);
end;
end;

/
show errors;
/


Re: insert else delete proc pls edit [message #197693 is a reply to message #197688] Thu, 12 October 2006 06:01 Go to previous message
Frank
Messages: 7877
Registered: March 2000
Senior Member
is this a contest?
Who can have the most 'begin's in his procedure?

Ah, now I see the topic title. You want me to edit your procedure... nah, I won't, but I will give you some hints:
- read the sticky (first post of the forum)
- buy a book or read some documentation on plsql, because there are quite a number of errors in your code.
(e.g. quotes around delivery_id where you mean to use the variable, more begins than ends, no from clause in query, selects into IN parameters etc etc)
Previous Topic: RE:PL/SQL Table
Next Topic: Stored Procedure ORA-06550 error in Oracle XE
Goto Forum:
  


Current Time: Fri Apr 18 12:47:00 CDT 2014

Total time taken to generate the page: 0.08929 seconds