Re: Testing row existence in PL/SQL block?

Great solution.

Thomas Kyte wrote:

> 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 Sun Jul 22 2001 - 11:12:39 CDT