RETURNING INTO clause (PL/SQL)

INSERT, UPDATE, and DELETE statements that are appended with the optional RETURNING INTO clause can be compiled by the Db2® data server. When used in PL/SQL contexts, this clause captures the newly added, modified, or deleted values from executing INSERT, UPDATE, or DELETE statements, respectively.

Syntax

Description

insert-statement: Specifies a valid INSERT statement. An exception is raised if the INSERT statement returns a result set that contains more than one row.
update-statement: Specifies a valid UPDATE statement. An exception is raised if the UPDATE statement returns a result set that contains more than one row.
delete-statement: Specifies a valid DELETE statement. An exception is raised if the DELETE statement returns a result set that contains more than one row.
RETURNING *: Specifies that all of the values from the row that is affected by the INSERT, UPDATE, or DELETE statement are to be made available for assignment.
RETURNING expr: Specifies an expression to be evaluated against the row that is affected by the INSERT, UPDATE, or DELETE statement. The evaluated results are assigned to a specified record or fields.
INTO record: Specifies that the returned values are to be stored in a record with compatible fields and data types. The fields must match in number, order, and data type those values that are specified with the RETURNING clause. If the result set contains no rows, the fields in the record are set to the null value.
INTO field: Specifies that the returned values are to be stored in a set of variables with compatible fields and data types. The fields must match in number, order, and data type those values that are specified with the RETURNING clause. If the result set contains no rows, the fields are set to the null value.

Examples

The following example shows a procedure that uses 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;

This procedure returns the following sample output:

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 shows a procedure that uses the RETURNING INTO clause with 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;

This procedure returns the following sample output:

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