Statement attributes (PL/SQL)

SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT are PL/SQL attributes that can be used to determine the effect of an SQL statement.

• The SQL%FOUND attribute has a Boolean value that returns TRUE if at least one row was affected by an INSERT, UPDATE, or DELETE statement, or if a SELECT INTO statement retrieved one row. The following example shows an anonymous block in which a row is inserted and a status message is displayed.

  BEGIN
      INSERT INTO emp (empno,ename,job,sal,deptno)
          VALUES (9001, 'JONES', 'CLERK', 850.00, 40);
      IF SQL%FOUND THEN
          DBMS_OUTPUT.PUT_LINE('Row has been inserted');
      END IF;
  END;

• The SQL%NOTFOUND attribute has a Boolean value that returns TRUE if no rows were affected by an INSERT, UPDATE, or DELETE statement, or if a SELECT INTO statement did not retrieve a row. For example:

  BEGIN
      UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9000;
      IF SQL%NOTFOUND THEN
          DBMS_OUTPUT.PUT_LINE('No rows were updated');
      END IF;
  END;

• The SQL%ROWCOUNT attribute has an integer value that represents the number of rows that were affected by an INSERT, UPDATE, or DELETE statement. For example:

  BEGIN
      UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001;
      DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT);
  END;