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

From: Keith Jamieson <Keith.Jamieson_at_phoenix.ie>
Date: Wed, 1 Aug 2001 15:13:24 +0100
Message-ID: <9k92i3$vru$1_at_kermit.esat.net>


You must be joking. You already have this information in the cursor attribute. Whether or not it is explicit or implicit;

ie
v_rowcount := SLQ%ROWCOUNT gives you what you want, without issuing another SQL statement.

SQL%ROWCOUNT works on both implicit and explicit cursors, so you can use it on either.

Keith Boulton wrote in message ...
>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 - 16:13:24 CEST

Original text of this message