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 t;
DROP SEQUENCE t_seq;
CREATE TABLE t (
id NUMBER()
description VARCHAR()
CONSTRAINT t_pk PRIMARY KEY (id)
);
CREATE SEQUENCE t_seq;
INSERT INTO t VALUES (t_seqnextval ONE);
INSERT INTO t VALUES (t_seqnextval TWO);
INSERT INTO t VALUES (t_seqnextval 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 tid%TYPE;
BEGIN
INSERT INTO t VALUES (t_seqnextval FOUR)
RETURNING id INTO l_id;
COMMIT;
DBMS_OUTPUTput_line(ID= || l_id);
END;
/
ID=
PL/SQL procedure successfully completed
SQL>The syntax is also available for update and delete statements
SET SERVEROUTPUT ON
DECLARE
l_id tid%TYPE;
BEGIN
UPDATE t
SET description = description
WHERE description = FOUR
RETURNING id INTO l_id;
DBMS_OUTPUTput_line(UPDATE ID= || l_id);
DELETE FROM t
WHERE description = FOUR
RETURNING id INTO l_id;
DBMS_OUTPUTput_line(DELETE ID= || l_id);
COMMIT;
END;
/
UPDATE ID=
DELETE ID=
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 tid%TYPE;
l_tab t_tab;
BEGIN
UPDATE t
SET description = description
RETURNING id BULK COLLECT INTO l_tab;
FOR i IN l_tabfirst l_tablast LOOP
DBMS_OUTPUTput_line(UPDATE ID= || l_tab(i));
END LOOP;
COMMIT;
END;
UPDATE ID=
UPDATE ID=
UPDATE ID=
PL/SQL procedure successfully completed
SQL>We can also use the RETURNING INTO clause in combination with bulk binds
SET SERVEROUTPUT ON
DECLARE
TYPE t_desc_tab IS TABLE OF tdescription%TYPE;
TYPE t_tab IS TABLE OF t%ROWTYPE;
l_desc_tab t_desc_tab := t_desc_tab(FIVE SIX SEVEN);
l_tab t_tab;
BEGIN
FORALL i IN l_desc_tabfirst l_desc_tablast
INSERT INTO t VALUES (t_seqnextval l_desc_tab(i))
RETURNING id description BULK COLLECT INTO l_tab;
FOR i IN l_tabfirst l_tablast LOOP
DBMS_OUTPUTput_line(INSERT ID= || l_tab(i)id ||
DESC= || l_tab(i)description);
END LOOP;
COMMIT;
END;
/
INSERT ID= DESC=FIVE
INSERT ID= DESC=SIX
INSERT ID= DESC=SEVEN
PL/SQL procedure successfully completed
SQL>This functionality is also available from dymanic SQL
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF tid%TYPE;
l_tab t_tab;
BEGIN
EXECUTE IMMEDIATE UPDATE t
SET description = description
RETURNING id INTO :l_tab
RETURNING BULK COLLECT INTO l_tab;
FOR i IN l_tabfirst l_tablast LOOP
DBMS_OUTPUTput_line(UPDATE ID= || l_tab(i));
END LOOP;
COMMIT;
END;
/
UPDATE ID=
UPDATE ID=
UPDATE ID=
PL/SQL procedure successfully completed
SQL>
