Oracle RETURNING INTO 用法示例

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.

 

SET SERVEROUTPUT ON

DECLARE

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=FIVE

INSERT ID=6 DESC=SIX

INSERT ID=7 DESC=SEVEN

 

6.This functionality is also available from dymanic SQL.

 

SET SERVEROUTPUT ON

DECLARE

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=1

UPDATE ID=2

UPDATE ID=3