INSERT..SELECT..RETURNING下FORALL的工作

INSERT..SELECT..RETURNING下FORALL的工作

INSERT..SELECT..RETURNING本身不支持FORALL的工作，不过可以使用以下方法。

102> CREATE TABLE t

  2  ( x INT

  3  , y CHAR(1)

  4  , z DATE );

Table created.

102>

102> CREATE TYPE ot AS OBJECT

  2  ( x INT

  3  , y CHAR(1)

  4  , z DATE );

  5  /

Type created.

102>

102> CREATE TYPE ntt AS TABLE OF ot;

  2  /

Type created.

102>

102> CREATE SEQUENCE s;

Sequence created.

102>

102>

102> --

102> -- Using the PLS-00436 workaround (see my 10g demo pages), we can now emulate

102> -- what we would expect INSERT..SELECT..RETURNING to do, but using

102> -- FORALL..INSERT..VALUES instead. The variable "nt_passed_in" represents the

102> -- collection parameter coming in from the middle-tier...

102> --

102>

102> DECLARE

  2 

  3     nt_passed_in ntt;

  4     nt_to_return ntt;

  5 

  6     FUNCTION pretend_parameter RETURN ntt IS

  7        nt ntt;

  8     BEGIN

  9        SELECT ot(NULL, 'X', SYSDATE) BULK COLLECT INTO nt

 10        FROM   dual

 11        CONNECT BY ROWNUM <= 5;

 12        RETURN nt;

 13     END pretend_parameter;

 14 

 15  BEGIN

 16 

 17     nt_passed_in := pretend_parameter();

 18 

 19     FORALL i IN nt_passed_in.FIRST .. nt_passed_in.LAST

 20        INSERT INTO t ( x, y, z )

 21        VALUES

 22        ( s.NEXTVAL

 23        , TREAT( nt_passed_in(i) AS ot ).y

 24        , TREAT( nt_passed_in(i) AS ot ).z

 25        )

 26        RETURNING ot( x, y, z )

 27        BULK COLLECT INTO nt_to_return;

 28 

 29     FOR i IN nt_to_return.FIRST .. nt_to_return.LAST LOOP

 30        DBMS_OUTPUT.PUT_LINE(

 31           'Sequence value = [' || TO_CHAR( nt_to_return(i).x ) || ']'

 32           );

 33     END LOOP;

 34 

 35  END;

 36  /

Sequence value = [1]

Sequence value = [2]

Sequence value = [3]

Sequence value = [4]

Sequence value = [5]

PL/SQL procedure successfully completed.