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