Tuesday, December 19, 2006

What is the point of SQL%NOTFOUND?

A recent post on Oracle Brains reminded me of how the above question used to puzzle me. Then one day I stumbled across the answer in the documentation while I was looking for an answer to some other problem. There are no secrets, only parts of the manual we have yet to read.

%NOTFOUND is one of the four cursor attributes (%FOUND, %ISOPEN and %ROWCOUNT are the others) which give us information about the state of our cursors. The more common situation is to monitor the status of an explicit cursor. In the following example (all examples were run on Oracle 9.2.0.6) I use the %NOTFOUND attribute to determine whether my fetch has returned a row:

SQL> declare
2 cursor cur is
3 select *
4 from emp
5 where ename = 'APC';
6 rec cur%rowtype;
7 begin
8 open cur;
9 fetch cur into rec;
10 if cur%notfound then
11 dbms_output.put_line('There is no employee called APC');
12 end if;
13 close cur;
14 exception
15 when no_data_found then
16 dbms_output.put_line('EXCEPTION: no data found');
17 end;
18 /
There is no employee called APC

PL/SQL procedure successfully completed.

SQL>

%FOUND does the reverse of %NOTFOUND, returning true if the fetch succeeded. %ISOPEN allows us to test whether a cursor is open before we attempt to close it. And %ROWCOUNT gives us a running total of the number of rows fetched so far.

The attributes SQL%NOTFOUND, SQL%FOUND, SQL%ISOPEN and SQL%ROWCOUNT perform exactly the same functions but for implicit cursors. Except that SQL%NOTFOUND is ignored, because the NO_DATA_FOUND exception takes precedence:

SQL> declare
2 rec emp%rowtype;
3 begin
4 select * into rec
5 from emp
6 where ename = 'APC';
7 if sql%notfound then
8 dbms_output.put_line('There is no employee called APC');
9 end if;
10 exception
11 when no_data_found then
12 dbms_output.put_line('EXCEPTION: no data found');
13 end;
14 /
EXCEPTION: no data found

PL/SQL procedure successfully completed.

SQL>

So what is the point of SQL%NOTFOUND? Well, selects are not the only type of statement which open a cursor. DML statements do too. In the following example I use that cursor attribute to see whether my statement updated any rows:

SQL> begin
2 update emp
3 set sal = 10000
4 where ename = 'APC';
5 if sql%notfound then
6 dbms_output.put_line('There is no employee called APC ...');
7 end if;
8 exception
9 when no_data_found then
10 dbms_output.put_line('EXCEPTION: no data found');
11 end;
12 /
There is no employee called APC ...

PL/SQL procedure successfully completed.

SQL>

I could have used if sql%rowcount = 0 to achieve the same effect.

The PL/SQL documentation has more information on these attributes, including some useful advice on the positioning of calls in relation to implicit cursors.

4 comments:

Noons said...

"So what is the point of SQL%NOTFOUND? Well, selects are not the only type of statement which open a cursor. DML statements do too."


Thanks for pointing this out. I've lost count of the number of times I've seen folks using a SELECT in PL/SQL to find out if an UPDATE will update any rows! This is a fundamental reason for the existence of these cursor status variables.

Anonymous said...

if the string 'There is no employee called APC' will never be displayed, the use of cur%notfound for select statement is useless...
am i right?

Anonymous said...

yes its useless

YuEnNnZ said...

use
"set serveroutput on" Command ...