Re: PL/SQL Question

From: Andrey A. Efimov <efa_at_leaves.spb.su>
Date: 1997/02/12
Message-ID: <01bc18d9$7ecd12b0$9a2b7dc1_at_efimov>#1/1


> What is wrong with the code?
>
> ***************************
>
>
> CREATE OR REPLACE PROCEDURE UpdateHours(
> work_essn works_on.essn%TYPE,
> work_pno works_on.pno%TYPE,
> work_hrs works_on.hours%TYPE)
> IS
> work_rec works_on%ROWTYPE;
> BEGIN
> SELECT essn, pno, hours INTO work_rec FROM works_on
> WHERE essn = work_essn AND pno = work_pno;
> IF SQL%NOTFOUND THEN
> INSERT INTO works_on(essn, pno, hours)
> VALUES(work_essn, work_pno, work_hrs);
> COMMIT;
> ELSE
> UPDATE works_on SET hours = work_hrs;
> COMMIT;
> END IF;
> END UpdateHours;
> /
> SHOW ERRORS
if select return without record it raise exception NO_DATA_FOUND and line if SQL%Notfound does not work. You can write like:
> CREATE OR REPLACE PROCEDURE UpdateHours(
> work_essn works_on.essn%TYPE,
> work_pno works_on.pno%TYPE,
> work_hrs works_on.hours%TYPE)
> IS
> work_rec works_on%ROWTYPE;
> BEGIN

       

> SELECT essn, pno, hours INTO work_rec FROM works_on
> WHERE essn = work_essn AND pno = work_pno;
> UPDATE works_on SET hours = work_hrs;
> COMMIT;
exception [Quoted] when NO_DATA_FOUND then INSERT INTO works_on(essn, pno, hours)
> VALUES(work_essn, work_pno, work_hrs);
> COMMIT;
> END UpdateHours;
> /

 but best result will be if you write:
> CREATE OR REPLACE PROCEDURE UpdateHours(
> work_essn works_on.essn%TYPE,
> work_pno works_on.pno%TYPE,
> work_hrs works_on.hours%TYPE)
> IS
> work_rec works_on%ROWTYPE;
> BEGIN
       Update works_on set hours = work_hrs;
       if SQL%rowcount = 0 then
           insert into works_on(essn, pno, hours)

> VALUES(work_essn, work_pno, work_hrs);

   end if;
> COMMIT;
> END UpdateHours;
> /
Received on Wed Feb 12 1997 - 00:00:00 CET

Original text of this message