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 ONDECLARE 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=4PL/SQL procedure successfully completed.SQL>
The syntax is also available for update and delete statements.
SET SERVEROUTPUT ONDECLARE 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=4DELETE ID=4PL/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 ONDECLARE 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=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQL>
We can also use the RETURNING INTO clause in combination with bulk binds.
SET SERVEROUTPUT ONDECLARE TYPE t_desc_tab IS TABLE OF t1.description%TYPE; TYPE t_tab IS TABLE OF t1%ROWTYPE; l_desc_tab t_desc_tab := t_desc_tab('FIVE', 'SIX', 'SEVEN'); l_tab t_tab;BEGIN FORALL i IN l_desc_tab.first .. l_desc_tab.last INSERT INTO t1 VALUES (t1_seq.nextval, l_desc_tab(i)) RETURNING id, description BULK COLLECT INTO l_tab; FOR i IN l_tab.first .. l_tab.last LOOP DBMS_OUTPUT.put_line('INSERT ID=' || l_tab(i).id || ' DESC=' || l_tab(i).description); END LOOP; COMMIT;END;/INSERT ID=5 DESC=FIVEINSERT ID=6 DESC=SIXINSERT ID=7 DESC=SEVENPL/SQL procedure successfully completed.SQL>
This functionality is also available from dymanic SQL.
SET SERVEROUTPUT ONDECLARE TYPE t_tab IS TABLE OF t1.id%TYPE; l_tab t_tab;BEGIN EXECUTE IMMEDIATE 'UPDATE t1 SET description = description RETURNING id INTO :l_tab' RETURNING 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=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQL>
For more information see:
Hope this helps.
–End–
