1) P5
ID DESCR QUNTY
---------
---------- ----------
101 Printer 5
102 RAM 100
103 Pen drive 200
104 Processor 10
2) P6
ID DESCR QUNTY
----------
---------- ----------
101 Printer 2
102 RAM 4
103 Pen drive 5
104 Processor 1
105 Scanner 10
INPUT
create
or replace package item is
Procedure
Pr1(ii in out number);
Procedure
Pr2(Pro21 in number,Pro22 in varchar2,Pro23 in number);
Procedure
Pr3(Pro31 in number,Pro32 in number);
end
item;
/
create
or replace package body item is
Procedure
Pr1(ii in out number) is
temp number(5);
begin
select id into temp from P5
where id=ii;
ii:=1;
exception
when NO_DATA_FOUND then
ii:=0;
end;
Procedure
Pr2(Pro21 in number,Pro22 in varchar2,Pro23 in number) is
begin
insert into P5
values(Pro21,Pro22,Pro23);
end;
Procedure
Pr3(Pro31 in number,Pro32 in number) is
begin
update P5 set QUNTY=QUNTY+Pro31 where id=pro31;
end;
end
item;
/
declare
cursor cur1 is select * from P6;
temp number(5);
begin
for buffer in cur1
loop
temp:=buffer.id;
item.Pr1(temp);
if temp=1 then
item.Pr3(buffer.id,buffer.QUNTY);
else
item.Pr2(buffer.id,buffer.DESCR,buffer.QUNTY);
end if;
end loop;
commit;
end;
/
OUTPUT
PL/SQL procedure successfully
completed.
Table:- P3
ID DESCR QUNTY
----------
---------- ----------
101 Printer 106
102 RAM 202
103 Pen drive 303
104 Processor 114
105 Scanner 10
0 Comments