Re: Store Procedure Questions

Toh Hong Giep <thonggie_at_mercury.starnet.gov.sg> wrote:
: I have a store procedure which made use of the 'SQL%FOUND' and
: 'SQL%NOTFOUND' attributes after a select statement.
:
: However this doesn't seemed to work as expected. When there is no
: data, the store procedure will terminate abnormally giving the error that
: no data is found!! I did not have an exception statement for the store
: procedure. My code goes something like this:

Geneally we put the select statment in its own BEGIN ... END block with a exception handler to catch the NO_DATA_FOUND.

	... (code in the stored procedure)
	BEGIN
	   SELECT ...
	EXCEPTION
	   WHEN NO_DATA_FOUND THEN
	      ... (default processing)
	END;
	... (rest of the oode)

We do use the SQL%NOTFOUND after we do an UPDATE statment in our data loaders.

	UPDATE ...;
	IF SQL%NOTFOUND THEN
	   INSERT ...;
	END IF;

Sometimes we do a count on the results before we do the actual SELECT.

	SELECT COUNT('X')
	INTO cnt
	...;
	IF cnt = 1 THEN
	   SELECT ...
	ELSIF cnt > 1 THEN
	   ...
	ELSE
	   ...
	END IF;

Or if we only want to know if a value exists we do:

	SELECT COUNT('X')
	INTO cnt
	FROM DUAL
	WHERE EXISTS (SELECT 1 ...);
	IF cnt <> 0 THEN
	   SELECT ...
	ELSE
	   ...
	END IF;

I hope this helps.

Later,
Steven
http://www.blkbox.com/~swhatley/

                      _|_  |  _|_   "I am the way and the truth and
Steven Whatley         | --|-- |    the life.  No one comes to the
swhatley_at_blkbox.com    |   |   |    Father except through me."
Houston, Texas             |            -- Jesus Christ (John 14:6 NIV)
                           |