BEGIN..SELECT..END LOOPS
Date: Sat, 19 Feb 94 16:37:00 -0500
Message-ID: <1d.5385.2378.0NEDF8D4_at_synapse.org>
What is the difference between using the following to check for no data found conditions in SQL*FORMS SELECT statements? How would you know which one to use?
#1:
DECLARE
DUMMY CHAR(1);
BEGIN
SELECT 'X' INTO DUMMY...
<success condition>
EXCEPTION
WHEN NO_DATA_FOUND THEN
<failure condition>
END;
#2:
SELECT 'x' INTO DUMMY...
IF SQL%NOTFOUND THEN
<failure condition>
ELSE
<success condition>
END IF;
#3:
DECLARE
INSTANCE_COUNT NUMBER;
BEGIN
INSTANCE_COUNT := 0;
SELECT COUNT(*) INTO INSTANCE_COUNT....
IF INSTANCE_COUNT < 1 THEN
<failure condition>
ELSE
<success condition>
END IF;
Obviously #1 is preferred for simple (non-looping) problems. You don't
want a scenerio where you have BEGIN..SELECT..BEGIN..SELECT..EXCEPTION
..END...EXCEPTION..END. And I realize that all of the examples are
idea in the instances that you do not want to retrieve any rows from
the database. How would you do a check, if there are multiple SELECT
statements, in one trigger if you want to retrieve data? I suspect it
is the BEGIN...SELECT..BEGIN...etc loop, which would be very complex.
e-mail: Doug.Hewko_at_Synapse.org
--- þ 1st 1.11 #574 þ Divers train to do it.Received on Sat Feb 19 1994 - 22:37:00 CET