Get the Result Status
There are some properties that can be used to confirm the execution of the command. SQL%FOUND is a boolean property that returns "true" when an INSERT, UPDATE, or DELETE command does a valid operation on a row, or when a SELECT INTO command takes out more than one row.
The following anonymous block inserts a row, and then displays the result of the insert operation.
\set PLSQL_MODE on CREATE OR REPLACE PROCEDURE emp_insert() IS 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; / \set PLSQL_MODE off postgres=# select emp_insert(); NOTICE: Row has been inserted EMP_INSERT ------------ (1 row) SQL%ROWCOUNT provides the number of valid operations performed on a row by the INSERT, UPDATE or DELETE commands. The following example updates the row inserted in the previous example and displays the value of SQL%ROWCOUNT.
\set PLSQL_MODE on CREATE OR REPLACE PROCEDURE emp_update() IS BEGIN UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001; DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT); END; / \set PLSQL_MODE off postgres=# select emp_update(); NOTICE: # rows updated: 1 EMP_UPDATE ------------ (1 row) SQL%NOTFOUND is the opposite of SQL%FOUND, and returns 'true' if the INSERT, UPDATE or DELETE command was unsuccessful on the record, or if the SELECT INTO command did not retrieve any data.
\set PLSQL_MODE on CREATE OR REPLACE PROCEDURE emp_update() IS 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; / \set PLSQL_MODE off postgres=# select emp_update(); NOTICE: No rows were updated EMP_UPDATE ------------ (1 row) 
Top comments (0)