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