How To Write PL/SQL block using Package.

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

Post a Comment

0 Comments