Re: BEGIN..SELECT..END LOOPS

In a previous article, doug.hewko_at_synapse.org (Doug Hewko) says:

>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.
>

I must say that I'm confused. By multiple SELECT statements, do you mean different SELECT statements or the same? And where do you see a loop? Anyway, I'd use a cursor for loop for looping constructs:

declare
  cursor loop_cur is
    select * from table where...;
  found boolean := FALSE;
begin
  for c in loop_cur loop
    found := TRUE;
    <success condition>
  end loop
  if not found then -- you only need this if you are doing special error     <failure condition> -- handling.
  end if;
end;

Otherwise, I'd use your second example, because then you don't have to deal with exception handlers, just IF..THEN..ELSE constructs. And if you want to have more that one query, probably using cursors is clearest.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things