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

From: Keith Boulton <kboulton_at_ntlunspam-world.com>
Date: Wed, 1 Aug 2001 11:24:05 +0100
Message-ID: <HHQ97.2465$io3.8200_at_news11-gui.server.ntli.net>


Another approach is to use:

select count(*) into RowCount. where ... and rownum < 3;

This will return 0,1 or 2 and requires less typing. I find too many nested blocks can make the code more difficult to read.

"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?
>
>
> --
> Phil Singer | psinger1_at_chartermi.net
> Oracle DBA
>
> Go Wings!!!!!!!
Received on Wed Aug 01 2001 - 12:24:05 CEST

Original text of this message