Oracle: RETURNING clause

Oracle: RETURNING clause

RETURNING is usually used in combination with DML statements. (Insert update delete)

Usage:

UPDATE table_name SET expr1
RETURNING column_name
INTO xxx

INSERT: returns the added value.
UPDATE: return the updated value.

DELETE: returns the value before deletion.

RETURNING can be used in sqlplus and plsql again.

For plsql, the preceding Code indicates that xxx is the declared variable name.

For sqlplus, xxx can be a variable, that is

VARIABLE var_name varchar2 (10)
UPDATE table_name SET expr1
RETURNING column_name INTO: var_name;

Here: var_name uses the Bind Variable.

In addition, RETURNING seems to be applicable

Insert into values supports RETURNING

Insert into select, and MERGE statements do not support RETURNING.

In addition, RETURNING can be combined with bulk collect (batch binding, and FORALL)

DECLARE
TYPE table_type is table of column_name % TYPE;
V_tab table_type;
BEGIN
UPDATE table_name
SET expr1
RETURNING column_name bulk collect into v_tab;

FOR I IN v_tab.first... v_tab.last LOOP
DBMS_OUTPUT.put_line (l_tab (I ));
End loop;

COMMIT;
END;
/