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

Bowing to the Masters:

Why not do a count first?
select count(*) into :fred where ......
if fred = 0 then

    business stuff
elseif fred = 1 then

    select .... into ......
    other business stuff
else

    raise/process error
end if

I realize two calls/selects is slower than 1, but it avoids the 'too many values' condition that can result from the select into with multiple response, unless you are always processing array values.

nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote in message news:<3b67cdff.1879109_at_news>...
> On Tue, 31 Jul 2001 23:00:48 -0400, Phil Singer
> <psinger1_at_chartermi.net> wrote:
>
> >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).
>
> Agreed...
>
> >
> >2) Continue to code explicit cursors (Tom has convinced me
> >that this is a mistake).
>
> Yup. Not needed. Can do, but not needed.
>
> >
> >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.
>
> Sure. Nested blocks are part and parcel of good PL/SQL coding. It's
> amazing just how much can be achieved with them.
>
> Another thing you can do is use the SQL%ROWCOUNT standard variable.
> Returns the number of rows of the last executed implicit SQL
> statement, whatever it may be.
>
> In your case, insert this between the <g_flag = '1';> and the
> <EXCEPTION> lines:
> if (SQL%ROWCOUNT = whatever rows you're after) then
> do whatever you want in that case;
> end if;
>
> repeat as needed, and you're sailing.
>
> Another useful technique is to use your own exceptions. Immediately
> after the CREATE PACKAGE BODY stuff define your specific exceptions.
> Eg:
>
> ONE_ROW exception; -- Handles one row returned.
> TWO_ROWS exception; -- Handles two rows returned.
> etc,etc.
>
> Now in your if(SQL%ROWCOUNT) stuff you can simply say:
>
> raise ONE_ROW;
> or
> raise TWO_ROW;
> or
> raise <whatever>
>
> and have an exception in an outer block now be triggered by it:
>
> exception
> when ONE_ROW then
> do stuff for single row;
> when TWO_ROW then
> do stuff for two rows;
> etc,etc.
>
> in here, "do_stuff" can be a nested block with heaps of stuff.
>
> >
> >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.
>
> Get them a copy of Steven Feuerstein's Advanced PL/SQL programming
> with Packages. Worth its weight in gold for a PL/SQL shop.
>
> >stumbled onto the proper approach, and I have just been
> >dumb and foolish up until now?
> >
>
> Not dumb and foolish at all. No one comes to this world all-knowing.
> The simple fact you worked out the solution proves you're not any of
> those!
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Wed Aug 01 2001 - 21:26:24 CEST