The INSERT, UPDATE, and DELETE statements may be appended by the optional RETURNING INTO clause. This clause allows the SPL program to capture the newly added, modified, or deleted values from the results of an INSERT, an UPDATE, or a DELETE statement.
The following example shows the syntax:
{ insert | update | delete }
RETURNING { * | expr_1 [, expr_2 ] ...}
INTO { record | field_1 [, field_2 ] ...} ;
insert is a valid INSERT statement. update is a valid UPDATE statement. delete is a valid DELETE statement. If * is specified, the values from the row affected by the INSERT, UPDATE, or DELETE statement are made available for assignment to the record or fields to the right of the INTO keyword. (Note that the use of * is an extension for PolarDB for PostgreSQL(Compatible with Oracle) and is not compatible with Oracle databases.) expr_1, expr_2... are expressions evaluated upon the row affected by the INSERT, UPDATE, or DELETE statement. The evaluated results are assigned to the record or fields to the right of the INTO keyword. record is the identifier of a record that must contain fields that match in number and order, and are data type compatible with the values in the RETURNING clause. field_1, field_2,... are variables that must match in number and order, and are data type compatible with the set of values in the RETURNING clause.
If the INSERT, UPDATE, or DELETE statement returns a result set with more than one row, an exception is thrown with the message of "SQLCODE 01422, query returned more than one row." If no rows are in the result set, the variables following the INTO keyword are set to null.
The following example is a modification of the emp_comp_update procedure introduced in UPDATE, with the addition of the RETURNING INTO clause:
CREATE OR REPLACE PROCEDURE emp_comp_update (
p_empno IN emp.empno%TYPE,
p_sal IN emp.sal%TYPE,
p_comm IN emp.comm%TYPE
)
IS
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;
v_sal emp.sal%TYPE;
v_comm emp.comm%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno
RETURNING
empno,
ename,
job,
sal,
comm,
deptno
INTO
v_empno,
v_ename,
v_job,
v_sal,
v_comm,
v_deptno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
DBMS_OUTPUT.PUT_LINE('New Salary : ' || v_sal);
DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
The following example shows the output from this procedure (assuming that employee 9503 created by the emp_insert procedure still exists within the table):
EXEC emp_comp_update(9503, 6540, 1200);
Updated Employee # : 9503
Name : PETERSON
Job : ANALYST
Department : 40
New Salary : 6540.00
New Commission : 1200.00
The following example is a modification of the emp_delete procedure, with the addition of the RETURNING INTO clause using record types:
CREATE OR REPLACE PROCEDURE emp_delete (
p_empno IN emp.empno%TYPE
)
IS
r_emp emp%ROWTYPE;
BEGIN
DELETE FROM emp WHERE empno = p_empno
RETURNING
*
INTO
r_emp;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || r_emp.empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
DBMS_OUTPUT.PUT_LINE('Manager : ' || r_emp.mgr);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
DBMS_OUTPUT.PUT_LINE('Commission : ' || r_emp.comm);
DBMS_OUTPUT.PUT_LINE('Department : ' || r_emp.deptno);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
The following example shows the output from this procedure:
EXEC emp_delete(9503);
Deleted Employee # : 9503
Name : PETERSON
Job : ANALYST
Manager : 7902
Hire Date : 31-MAR-05 00:00:00
Salary : 6540.00
Commission : 1200.00
Department : 40