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.
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;
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.