Oracle DML 子句 RETURNING INTO 用法示例

 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 ONDECLARE  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=4PL/SQL procedure successfully completed.SQL>

The syntax is also available for update and delete statements.

SET SERVEROUTPUT ONDECLARE  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=4DELETE ID=4PL/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 theBULK COLLECT clause.

SET SERVEROUTPUT ONDECLARE  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=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQL>

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

SET SERVEROUTPUT ONDECLARE  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=FIVEINSERT ID=6 DESC=SIXINSERT ID=7 DESC=SEVENPL/SQL procedure successfully completed.SQL>

This functionality is also available from dymanic SQL.

SET SERVEROUTPUT ONDECLARE  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=1UPDATE ID=2UPDATE ID=3PL/SQL procedure successfully completed.SQL>

For more information see:

• RETURNING INTO Clause

Hope this helps. Regards Tim...

from: http://www.oracle-base.com/articles/misc/dml_returning_into_clause.php

--End--