BEGIN..SELECT..END LOOPS

From: Doug Hewko <doug.hewko_at_synapse.org>
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

Original text of this message