Home » SQL & PL/SQL » SQL & PL/SQL » Procedure: insert else delete (merged)
|
|
|
|
| insert else delete proc pls edit [message #197688 is a reply to message #197668] |
Thu, 12 October 2006 05:46   |
oracle_coorgi
Messages: 188
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 |
Frank
Messages: 7901
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)
|
|
|
|
Goto Forum:
Current Time: Wed Dec 04 18:57:27 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
