Re: Store Procedure Questions

Ken Denny wrote:
> Toh Hong Giep wrote:
> > I have a store procedure which made use of the 'SQL%FOUND' and
> > 'SQL%NOTFOUND' attributes after a select statement.
 [snip]
> > SELECT .....
> > If SQL%FOUND then
> > .....
> > End if;

 [snip]
> I have not found a use for SQL%FOUND and SQL%NOTFOUND. The way to do
> this is to use an exception clause, but don't put it at the end of the
> procedure. Instead, place a BEGIN ... EXCEPTION ... END; structure
> around every SELECT where you need to check whether it was found or
> not.

The reason SQL%FOUND doesn't work in the example given is because it only works with cursor fetches, not explicit selects.

Remember that it is more efficient to define all queries in PL/SQL as explicit cursors, because implicit cursors cause extra fetches to the database.

So the above example could be re-written as:

DECLARE
    CURSOR a IS

        SELECT ......     b VARCHAR2(20); -- or whatever
BEGIN
    OPEN a
    FETCH a INTO b;
    IF SQL%FOUND THEN
        .......
    ELSE
        .......
    END IF;
END; Received on Tue Jun 10 1997 - 00:00:00 CDT