Is there another way (Was: benchmarking, which statement is faster

Thomas Kyte wrote:

[Old discussion of a SELECT... INTO... vrs. Explicit CURSORS snipped]

Sorry to revive this thread, but it left me wondering....

Tom stated that if one wanted to do a SELECT..INTO.. in PL/SQL using cursors, one should proceed as follows (but the direct SELECT...INTO... was faster and easier to read)

>
> loop
> open c1
> fetch c1
> if (not c1%found ) then raise no_data_found; end if;
> fetch c1
> if ( c1%found ) then raise too_many_rows; end if;
> close c1;
> end loop;
>

Now I have often found myself replacing a SELECT...INTO... with a cursor for the following reason: the business logic surrounding the zero row case is not an exception. That, is, the business logic is

   fetch c1

   If no rows, do something.

   If one row, do something else

   If two or more rows, raise an exception.

And a SELECT...INTO.. will raise the no_data_found exception, sending me into the exception handler,
while a cursor will set the %notfound condition, which my program can trap and proceed onward.

Thinking about this, I have come up with only 3 ways to take care of this situation:

1. Get the big-3 auto company I work for to cease putting so many intelligent keys into their data model, so this situation doesn't come up (not good).
2. Continue to code explicit cursors (Tom has convinced me that this is a mistake).
3. Set this piece of logic into a new block (something like:)

   BEGIN
   main block

         .
         .
      BEGIN;
         SELECT...INTO...(global variables here);
         g_flag = '1';
      EXCEPTION
         WHEN NO_DATA_FOUND
           g_flag = '0';
      END;

       .
       .
      

Where the main block now knows if it has 0 or 1 row found, and also has the columns in the row. If 2 or more rows are found, the error will be propataged to the exception handler in the main block.

My problem with this approach is that I don't think any of the other developers I know have ever seen a PL/SQL routine with a nested block.

So, my question is, is there a 4th approach? Or have I stumbled onto the proper approach, and I have just been dumb and foolish up until now?

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!