Re: Testing row existence in PL/SQL block?

On Fri, 20 Jul 2001 22:04:44 GMT, gamma_at_clark.net (Gamma) wrote:

>I have a PL/SQL block that looks sorta like this:
>
>declare
> e int;
>begin
> select count(*) into e from SomeTable
> where SomeConditions;
> if e=0
> DoSomething
> else
> DoSomethingElse
> end if;
> DoMoreStuff;
>end;
>
>In summary, the block does one of two things depending on whether
>at least one row exists in SomeTable with certain conditions. The
>trouble is that the block seems inefficient; it counts all of the
>rows, but actually I can stop as soon as I find one. (Often it's
>even the case that there will be one row at most.)
>
>What would be the proper way to do this? I considered using a
>slightly different select-into statement, and then using exception
>handling to catch NO_DATA_FOUND, but then I couldn't return to the
>main execution block, so that's out.
>
>Another way might be to declare a cursor with the appropriate
>select, and then test it with %NOTFOUND. But often the select I
>create will use values obtained partway through the execution block,
>and by then it's too late to declare the cursor.
>
>Any ideas?
>
>--
>
>Paul Brinkley
>gamma_at_clark.net
>

Hi Paul,

This should do the trick :

declare
  e int;
begin
  select count(*) into e from SomeTable
    where Somecondition AND ROWNUM=1;
/* e is either 0 or 1 */
if e=0

    DoSomething
  else
    DoSomethingElse
  end if;
  DoMoreStuff;
end;

Regards,
Kenneth Koenraadt
Systems Consultant
Oracle DBA
plovmand@<no-spam>hotmail.com Received on Sat Jul 21 2001 - 05:57:15 CDT