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.