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

The point is that count(*) ALWAYS returns a row, so you never have to worry about handling no_data_found errors. Likewise it will return 2 if there is more than one row, so you don't have to perform a second fetch or handle a too_many_rows error.

"Keith Jamieson" <Keith.Jamieson_at_phoenix.ie> wrote in message news: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 - 10:52:14 CDT