Re: Testing row existence in PL/SQL block?

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

Presumably your table has a primary key which is, by definition, not null so ...

BEGIN
   SELECT pk_field
   INTO x
   FROM some_table
   WHERE some_condition
   AND rownum = 1;

   do_something
EXCEPTION
   WHEN NODATAFOUND THEN
         do_something else
END; Daniel A. Morgan Received on Sat Jul 21 2001 - 01:15:38 CDT