Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Testing row existence in PL/SQL block?
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.netReceived on Fri Jul 20 2001 - 17:04:44 CDT