OracleDML子句RETURNINGINTO

　　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>

From:http://tw.wingwit.com/Article/program/Oracle/201311/18329.html