Several attributes can be used to determine the effect of a statement. SQL%FOUND has a Boolean value. SQL%FOUND returns TRUE if at least one row was affected by an INSERT, an UPDATE, or a DELETE statement or if a SELECT INTO statement retrieved one or more rows.

The following anonymous block inserts a row and then displays the fact that the row has been inserted:

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;

Row has been inserted

SQL%ROWCOUNT provides the number of rows affected by an INSERT, an UPDATE, a DELETE, or a SELECT INTO statement. The SQL%ROWCOUNT value is returned as a BIGINT data type. The following example updates the row that was just inserted and displays SQL%ROWCOUNT:

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

# rows updated: 1

SQL%NOTFOUND is the opposite of SQL%FOUND. SQL%NOTFOUND returns TRUE if no rows were affected by an INSERT, an UPDATE, or a DELETE statement or if a SELECT INTO statement retrieved no rows.

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;

No rows were updated