How To Write PL/SQL block using Function & Procedure.

1)  P1

      ID DESCR           QUNTY

---------- ---------- ----------

       101 Printer             5

       102 RAM               100

       103 Pen drive         200

       104 Processor          10

2) P2

        ID DESCR           QUNTY

---------- ---------- ----------

       101 Printer             2

       102 RAM                 4

       103 Pen drive           5

       104 Processor           1

       105 Scanner            10

INPUT

create or replace function Fu1(ii in number) return number is temp number(10);

begin

          select id into temp from P1 where id=ii;

                   return 1;

Exception

          when NO_DATA_FOUND then

                   return 0;

          end;

          /

declare

          cursor cur is select * from P2;

          temp number(5);

begin

          for Buffer in cur

          loop

          temp:=Fu1(Buffer.id);   

          if temp=1  then

                   update P1 set qunty=qunty+buffer.Qunty where id=Buffer.id;

          else

                   insert into P1 values(Buffer.id,Buffer.descr,Buffer.qunty);

          end if;

          end loop;

          commit;

          end;

          /

OUTPUT

PL/SQL procedure successfully completed.

Table:- P1

        ID DESCR           QUNTY

---------- ---------- ----------

       101 Printer             7

       102 RAM               104

       103 Pen drive         205

       104 Processor          11

       105 Scanner