Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Testing row existence in PL/SQL block?

Testing row existence in PL/SQL block?

From: Gamma <gamma_at_clark.net>
Date: Fri, 20 Jul 2001 22:04:44 GMT
Message-ID: <0W167.1225$ym4.45349@iad-read.news.verio.net>

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
Received on Fri Jul 20 2001 - 17:04:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US