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

Joe Maloney wrote:
>
> 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

You would want to have a "and rownum < 3" in the count, otherwise you are probaby doing too much work for rows 4 .. n (if they exist)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."