Returnning Into 总结

ORACLE的DML语句中可以指定RETURNING语句。使用起来也很简单，和SELECT INTO语句没有多大区别。RETURNING语句的使用在很多情况下可以简化PL/SQL编程。

　　DELETE操作：RETURNING返回的是DELETE之前的结果；
　　INSERT操作：RETURNING返回的是INSERT之后的结果；
　　UPDATE操作：的RETURNING语句是返回UPDATE操作之后的结果。

　　[color=indigo]INSERT INTO SELECT语句不支持。
　　MERGE语句不支持RETURNING语句。[/color]

Example:
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

　　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

　　PL/SQL procedure successfully completed.

　　SQL>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

　　PL/SQL procedure successfully completed.

　　SQL>