%NOTFOUND, %ROWCOUNT and %ISOPEN attributes - Oracle

'SQL%NOTFOUND' returns 'true' if the most recent DML statement could not get successfully executed (quite the opposite of 'SQL%found'). We can also implement 'SQL%NotFound' in the above program as follows:

Declare
  v_empno emp.empno%type := &empno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal 
    Where empno=v_empno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Salary got Succesfully updated.');
  end if;
End;

If you carefully observe the above program, apart from replacing 'SQL%found' with 'SQL%notfound', I also swapped the DBMS_OUTPUT statements. This is because 'SQL%notfound' is negative.

If you ask me a question, which is the better of 'SQL%found' and 'SQL%notfound', it would be something like asking which eye is better! We require both, but we need to use them based on the situation.

Now, let us consider another example as following:

Declare
  v_deptno emp.deptno%type := &deptno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal
    Where deptno=v_deptno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Salary got Succesfully updated.');
  end if;
End;

The only difference is that I changed the 'empno' to 'deptno' in the above program. That means that the program gets 'deptno' and 'sal' from the user, and updates all of the employees within that department with the new salary provided. There is no doubt that the program works perfectly with 'SQL%notfound'.

But now the issue is not simply displaying the 'Succesfully updated.' message. I would also like to show the number of rows updated as part of the same message. Certainly the user will be gladder to see that than not. How do I achieve that? As you guessed, it is with 'SQL%rowcount' and the following program demonstrates that.

Declare
  v_deptno emp.deptno%type := &deptno;
  v_sal emp.sal%Type := &sal;
Begin
  Update emp set sal = v_sal
    Where deptno=v_deptno;
  if SQL%notfound then
    dbms_output.put_line('No employee found.');
  else
    dbms_output.put_line('Updated ' || SQL%rowcount || ' employees.');
  end if;
End;

Only the second DBMS_OUTPUT statement got modified with 'SQL%rowcount'. The 'SQL%rowcount' gives the number of rows affected by the most recent DML command within a PL/SQL program.

Coming to our last %ISOPEN attribute, it generally yields false, when used with SQL cursor. Oracle automatically closes the SQL cursor, once the DML statement finishes its execution. So, we will never get any chance to work with %ISOPEN by using SQL cursor. It is generally used with EXPLICIT cursors. My next article (part 10) will deal the concept of EXPLICIT cursors in detail.