Re: PL/SQL Question

Jessica Dancy <dancyj_at_pobox.upenn.edu> writes: > I am trying to get the following procedure to simply read a row from a
> table via a select statement, using an implicit cursor. If the table is
> empty, I want to add a new tuple, if the tuple already exists, I just
> want to modify the tuple.
>
> The procedure works if data already exists, yet I get an error message
> if no matches are found.
>
> 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
[Quoted] This code raises an exception which isn't handled, therefore the error. Try this 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;
	UPDATE works_on SET hours = work_hrs;
	COMMIT;

  THEN	INSERT INTO works_on(essn, pno, hours)
	VALUES(work_essn, work_pno, work_hrs);
	COMMIT;

Gerrit Scholten Received on Thu Feb 13 1997 - 00:00:00 CET