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

Phil Singer <psinger1_at_chartermi.net> wrote in message news:<3B6770E0.30C5BC40_at_chartermi.net>...
> 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;
> resume main block
> Test g_flag
> .
> .
>
> 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?

You have stumbled onto the correct approach, and the other developers should learn about nested blocks. It may be okay for an entry-level PL/SQL developer to not know about nested blocks, but if they've been around for more than a year or two, and if they don't know about nested blocks, they've led a sheltered life.

This isn't about execution speed. It's about what is clean and easy to read. I once had an argument with a co-worker who insisted explicit cursors were faster because "the book" said they were. We benchmarked them and found that implicit cursors were something like 2-5% faster. Big deal. That's not enough to matter. The bottom line is he added 30 or 40 lines to every program to "speed it up" when he was actually slowing it down. Received on Wed Aug 01 2001 - 09:33:16 CEST