1.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;

?

2.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

v_id t1.id%TYPE;

BEGIN

INSERT INTO t1

VALUES (t1_seq.nextval, 'FOUR')

RETURNING id INTO v_id;

COMMIT;

DBMS_OUTPUT.put_line('ID=' || v_id);

END;

/

?

ID = 4

?

3.The syntax is also available for update and delete statements.

?

SET SERVEROUTPUT ON

DECLARE

v_id t1.id%TYPE;

BEGIN

UPDATE t1

SET description = description

WHERE description = 'FOUR'

RETURNING id INTO v_id;

DBMS_OUTPUT.put_line('UPDATE ID=' || v_id);

DELETE FROM t1

WHERE description = 'FOUR'

RETURNING id INTO v_id;

DBMS_OUTPUT.put_line('DELETE ID=' || v_id);

COMMIT;

END;

/

?

UPDATE ID = 4

DELETE ID = 4

?

4.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;

v_tab t_tab;

BEGIN

UPDATE t1

SET description = description

RETURNING id BULK COLLECT INTO v_tab;

FOR i IN v_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

?

5.We can also use the RETURNING INTO clause in combination with bulk binds.