AntDB-Oracle Compatibility Developer’s Manual P4–21

Use the RETURNING INTO clause

A RETURNING INTO clause can be added after the INSERT, UPDATE, and DELETE commands. The purpose of this clause is to allow the SPL program to capture the latest inserted, modified, or deleted values of the INSERT, UPDATE, or DELETE clauses, respectively.

The following is the syntax of this clause.

{ insert | update | delete }

RETURNING { * | expr_1 [, expr_2 ] ...}

INTO { record | field_1 [, field_2 ] ...};

insert, update, and delete are valid INSERT, UPDATE, and DELETE commands, respectively. If '*' is specified, then the records successfully operated by INSERT, UPDATE or DELETE commands can be assigned to the fields to the right of the record type or INTO keyword. (Note that "*" is an extension of AntDB and is not Oracle compatible). expr_1, expr_2... are expressions calculated with the records operated by INSERT, UPDATE or DELETE commands. The final result of the calculation is assigned to the record or field to the right of the INTO keyword. record is the identifier of the record. The fields in this record must match the number and order of occurrence of the values returned in the RETURNING INTO clause, and be compatible with their data types. field_1, field_2,... are variables that must match the number and order of the set of values in the RETURNING INTO clause, and be compatible with their data types.

If the INSERT, UPDATE or DELETE command returns a result set with multiple rows, an SQLCODE exception of 01422 is thrown, indicating that the query returns multiple rows. If there are no rows in the result set, the variable following the INTO keyword will be set to a null value.

Note: There is another way to use the RETURNING INTO clause that allows a result set containing multiple rows to be returned to a collection by using the BULK COLLECT clause.

Example:

Prepare test data.

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create a stored procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
    p_empno IN NUMBER,
    p_sal IN  NUMBER(7,2),
    p_comm IN NUMBER(7,2)
)
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;
/
\set PLSQL_MODE off

The following is the call and output of this stored procedure.

postgres=# select  emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Department : 20
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

In the following example, we add a RETURNING INTO clause to this procedure that uses the record type.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
    r_emp emp%ROWTYPE;
BEGIN
    DELETE FROM emp WHERE empno = p_empno
        RETURNING
            empno,
            ename,
            job,
            sal,
            comm,
            deptno
        INTO
            r_emp.empno,
            r_emp.ename,
            r_emp.job,
            r_emp.sal,
            r_emp.comm,
            r_emp.deptno;

    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('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;
/
\set PLSQL_MODE off

The following is the result of the execution of this procedure.

postgres=# select  emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Salary : 6540
NOTICE:  Commission : 1200
NOTICE:  Department : 20
 EMP_DELETE
------------

(1 row)