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

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 - 04:59:13 CDT