Re: PL/SQL Question
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
> END UpdateHours;
> 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;
> /
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