Explain PL/SQL cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN

How it works

Every cursor, explicit or implicit, exposes four attributes: %FOUND (TRUE if the last fetch returned a row), %NOTFOUND (its negation, and the usual loop-exit condition), %ROWCOUNT (the number of rows fetched, or affected by a DML, so far), and %ISOPEN (whether the cursor is currently open). For an implicit cursor — any bare DML or SELECT INTO — these are referenced as SQL%FOUND, SQL%ROWCOUNT, and so on, right after the statement.

DECLARE
  CURSOR c_emp IS SELECT employee_id FROM employees WHERE department_id = 90;
  v_id employees.employee_id%TYPE;
BEGIN
  OPEN c_emp;
  FETCH c_emp INTO v_id;
  IF c_emp%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('First row, total so far: ' || c_emp%ROWCOUNT);
  END IF;
  CLOSE c_emp;

  UPDATE employees SET salary = salary * 1.03 WHERE department_id = 90;
  DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);
END;
/

Related Questions