Oracle RETURNING INTO Ó÷¨Ê¾Àý

The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.
DROP TABLE t1;
DROP SEQUENCE t1_seq;
CREATE TABLE t1 (
id NUMBER(10),
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE SEQUENCE t1_seq;
INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE');
INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO');
INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE');
COMMIT;
When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.
SET SERVEROUTPUT ON
DECLARE
l_id t1.id%TYPE;
BEGIN
INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
RETURNING id INTO l_id;
COMMIT;
DBMS_OUTPUT.put_line('ID=' || l_id);
END;
/
ID=4
PL/SQL procedure successfully completed.
SQL>
The syntax is also available for update and delete statements.
SET SERVEROUTPUT ON
DECLARE
l_id t1.id%TYPE;
BEGIN
UPDATE t1
SET description = description
WHERE description = 'FOUR'
RETURNING id INTO l_id;
DBMS_OUTPUT.put_line('UPDATE ID=' || l_id);
DELETE from t1
WHERE description = 'FOUR'
RETURNING id INTO l_id;
DBMS_OUTPUT.put_line('DELETE ID=' || l_id);
COMMIT;
END;
/
UPDATE ID=4
DELETE ID=4
PL/SQL procedure successfully completed.
SQL>
When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF t1.id%TYPE;
l_tab t_tab;
BEGIN
UPDATE t1
SET description = description
RETURNING id BULK COLLECT INTO l_tab;
FOR i IN l_tab.first .. l_tab.last LOOP
DBMS_OUTPUT.put_line('UPDATE ID=' || l_tab(i));
END LOOP;
COMMIT;
END;
/
UPDATE ID=1
UPDATE ID=2
UPDATE ID=3
PL/SQL procedure successfully completed.
SQL>
We can also use the RETURNING INTO clause in combination with bulk binds.
SET SERVEROUTPUT ON