Re: Testing row existence in PL/SQL block?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 21 Jul 2001 05:50:07 -0700
Message-ID: <9jbtpv0l0d@drn.newsguy.com>

In article <0W167.1225$ym4.45349_at_iad-read.news.verio.net>, gamma_at_clark.net says...
>
>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?
>

A fast and EXPRESSIVE way to do this (a method that is somewhat self documenting) is:

declare
  e int;
begin
  select count(*) into e
    from dual
   WHERE EXISTS ( select null

                    from SomeTable
                   where SomeConditions );

  if e=0
    DoSomething
  else
    DoSomethingElse
  end if;
  DoMoreStuff;
end;

That will run your subquery only until it finds the FIRST occurrence in sometable and then stop (efficient). The WHERE EXISTS makes it clear that you are just "looking" for a row -- does a row exist, you don't really care what it is, just that it is there.

>--
>
>Paul Brinkley
>gamma_at_clark.net
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Jul 21 2001 - 07:50:07 CDT