Re: PL/SQL Question

From: Jerry Bull <gbbull_at_uswest.com>
Date: 1997/02/11
Message-ID: <3300FE6B.4A26_at_uswest.com>#1/1

Jessica Dancy wrote:
>
> 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;

You need to either user a cursor to do your select, or include an exception section that handles NO_DATA_FOUND.

 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; -- NO WHERE CLAUSE?????
         COMMIT;

 EXCEPTION
   When NO_DATA_FOUND Then

[Quoted] [Quoted]       INSERT INTO works_on (essn, pno, hours)
	values (work_essn, work_pno, work_hrs);
      commit;

 END UpdateHours;

Jerry.

-- 
----------------------------------------------------
Jerry Bull, Sr. MTS         Phone: 303-624-3073
U S WEST Technologies       Fax:   303-624-8462
931 14th Street, Suite 920  Email: gbbull_at_uswest.com
Denver, CO  80202
----------------------------------------------------

Received on Tue Feb 11 1997 - 00:00:00 CET