Cursor Attributes

Contact Us or call 1-877-932-8228
Cursor Attributes

Cursor Attributes

There are a number of cursor attributes that return data about the execution of DML statements (insert, update, delete, etc.). Each is prefixed with a percent sign. The %FOUND attribute returns TRUE if a DML statement or a SELECT INTO statement returned at least one row. The %NOTFOUND attribute returns the opposite of the %FOUND attribute. The %ROWCOUNT attribute returns the number of rows affected by a DML statement or returned by a SELECT INTO statement. In the case of these implicit cursors, the values of the cursor attributes are based upon the most recently executed SELECT INTO or DML statement. In the next section you'll see that there is one additional cursor attribute (%ISOPEN) that is not applicable for implicit cursors.

Code Sample:

Use-of-SQL-in-PLSQL/Demos/select_into_and_dml_with_rowcount.sql
declare
  v_first_name employees.first_name%type;
  v_last_name  employees.last_name%type;
begin

  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id = 100;
  dbms_output.put_line('#1 select into %rowcount=' ||
                       SQL%rowcount);
  dbms_output.put_line(v_first_name ||
                       ' ' ||
                       v_last_name);

  -- a dml statement and its attributes
  update employees
  set salary = salary * 1.1;
  dbms_output.put_line('Just updated ' ||
                       sql%rowcount ||
                       ' salaries.');

  rollback;  -- this is a dml statement that does NOT have 
             -- any implicit cursors associated with it.
  
  -- another valid employee id would be 101
  -- but let's use an invalid one to throw an error
  select first_name, last_name
  into v_first_name, v_last_name
  from employees
  where employee_id = 1;
  
  -- An error was thrown so we never get here.
  dbms_output.put_line('#2 select into %rowcount=' ||
                       SQL%rowcount);  

exception
 when NO_DATA_FOUND then
  dbms_output.put_line('Oops. Is the employee id valid?');
  dbms_output.put_line('No data found. NOTE: %rowcount=' ||
                       SQL%rowcount);
 when TOO_MANY_ROWS then
  dbms_output.put_line('Oops. Are you using a PK value?');
  dbms_output.put_line('Too many rows. NOTE: %rowcount=' ||
                       SQL%rowcount);  
end;

Code Explanation

Because each implicit cursor has the same name, SQL, the cursor attributes must be used as soon as possible after the implicit cursor's executing SELECT INTO or DML statement. Notice the value of %ROWCOUNT when no data is found. Now run the code again, changing the equal sign in the second SELECT INTO into a greater than sign. Notice the value of %ROWCOUNT when too many rows are returned.

Next