RETURNING INTO语句

我们可以在INSERT、UPDATE、DELETE或者动态SQL的RETURNING INTO子句中使用BULK COLLECT来进行批量数据绑定。

语法：

static_returning_clause ::=

static_returning_clause ::=
    RETURNING {column[,...n]} {into_clause | bulk_collect_into_clause}

dynamic_returning_clause ::=

dynamic_returning_clause::=
    RETURNING {into_clause | bulk_collect_into_clause}

说明：

static_returning_clause

静态RETURNING INTO语句可以用在DELETE、INSERT、UPDATE语句中。

dynamic_returning_clause

动态RETURNING INTO语句可以用在EXECUTE IMMEDIATE语句中。

如下实例，使用DELETE语句删除表中ID为5的列， 并将影响的行分别返回到相应类型的集合变量中，最后输出这些数据

示例1：静态RETURNING INTO语句

--  清理环境
DROP PROCEDURE proc;
DROP TABLE tab;

CREATE TABLE tab 
(
    ID INTEGER,
    NUM INTEGER,
    NAME VARCHAR2(15)
);
INSERT INTO tab VALUES(1, 11,'1A');
INSERT INTO tab VALUES(2, 21,'2A');
INSERT INTO tab VALUES(2, 22,'2B');
INSERT INTO tab VALUES(3, 31,'3A');
INSERT INTO tab VALUES(3, 32,'3B');
INSERT INTO tab VALUES(3, 33,'3C');
INSERT INTO tab VALUES(4, 41,'4A');
INSERT INTO tab VALUES(4, 42,'4B');
INSERT INTO tab VALUES(4, 43,'4C');
INSERT INTO tab VALUES(4, 44,'4D');
INSERT INTO tab VALUES(5, 51,'5A');
INSERT INTO tab VALUES(5, 52,'5B');
INSERT INTO tab VALUES(5, 53,'5C');
INSERT INTO tab VALUES(5, 54,'5D');
INSERT INTO tab VALUES(5, 55,'5E');

CREATE OR REPLACE PROCEDURE proc AS
    TYPE IDTAB IS VARRAY(10) OF tab.ID%TYPE;
    TYPE NUMTAB IS TABLE OF tab.NUM%TYPE INDEX BY PLS_INTEGER;
    TYPE NAMETAB IS TABLE OF tab.NAME%TYPE INDEX BY PLS_INTEGER;
    IDS IDTAB;
    NUMS NUMTAB;
    NAMES NAMETAB;
    ITERATIONS CONSTANT PLS_INTEGER := 5;
    K INT;
BEGIN
    DELETE FROM tab WHERE ID = 5 RETURNING ID,NUM,NAME 
        BULK COLLECT INTO IDS,NUMS,NAMES;
        
    --输出集合数据
    K := IDS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== IDS ==');
    DBMS_OUTPUT.PUT_LINE(IDS.COUNT);

    
    K := NUMS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NUMS ==');
    DBMS_OUTPUT.PUT_LINE(NUMS.COUNT);

    
    K := NAMES.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NAMES ==');
    DBMS_OUTPUT.PUT_LINE(NAMES.COUNT);

    
        DELETE FROM tab WHERE ID = 1 RETURNING ID,NUM,NAME 
        BULK COLLECT INTO IDS,NUMS,NAMES;
        
    --输出集合数据
    K := IDS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== IDS ==');
    DBMS_OUTPUT.PUT_LINE(IDS.COUNT);

    
    K := NUMS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NUMS ==');
    DBMS_OUTPUT.PUT_LINE(NUMS.COUNT);
    WHILE K IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NUMS(K));
        K := NUMS.NEXT(K);
    END LOOP;
    
    K := NAMES.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NAMES ==');
    DBMS_OUTPUT.PUT_LINE(NAMES.COUNT);
    WHILE K IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NAMES(K));
        K := NAMES.NEXT(K);
    END LOOP;

    
END;
/
EXEC proc;
== IDS ==
5
== NUMS ==
5
== NAMES ==
5
== IDS ==
1
== NUMS ==
1
DATA OF 1 IS 11
== NAMES ==
1
DATA OF 1 IS 1A


SELECT * FROM tab ORDER BY NAME;
ID(int)      |NUM(int)      |NAME(varchar)      |
----------------------------------------------
2            |21            |2A                 |
----------------------------------------------
2            |22            |2B                 |
----------------------------------------------
3            |31            |3A                 |
----------------------------------------------
3            |32            |3B                 |
----------------------------------------------
3            |33            |3C                 |
----------------------------------------------
4            |41            |4A                 |
----------------------------------------------
4            |42            |4B                 |
----------------------------------------------
4            |43            |4C                 |
----------------------------------------------
4            |44            |4D                 |
总数目：9

--删除
DROP PROCEDURE proc;
DROP TABLE tab;

示例2：动态RETURNING INTO语句

在动态SQL中使用BULK COLLECT语句用法相似，如下实例对tab表进行更新操作， 将表中的NUM和NAME列设置为新的值并将所有受影响的数据分别批量绑定到集合变量中， 最后输出全部集合数据

--  清理环境
DROP PROCEDURE proc;
DROP TABLE tab;

CREATE TABLE tab 
(
    ID INTEGER,
    NUM INTEGER,
    NAME VARCHAR2(15)
);
INSERT INTO tab VALUES(1, 11,'1A');
INSERT INTO tab VALUES(2, 22,'2B');
INSERT INTO tab VALUES(3, 33,'3C');
INSERT INTO tab VALUES(4, 44,'4D');
INSERT INTO tab VALUES(5, 55,'5E');

CREATE OR REPLACE PROCEDURE proc AS
    TYPE IDTAB IS VARRAY(20) OF tab.ID%TYPE;
    TYPE NUMTAB IS TABLE OF tab.NUM%TYPE INDEX BY PLS_INTEGER;
    TYPE NAMETAB IS TABLE OF tab.NAME%TYPE INDEX BY PLS_INTEGER;
    IDS IDTAB;
    NUMS NUMTAB;
    NAMES NAMETAB;
    ITERATIONS CONSTANT PLS_INTEGER := 5;
    K INT;
BEGIN
    EXECUTE IMMEDIATE 'UPDATE tab SET NUM = 100,NAME = ''NEW'' 
        RETURNING ID,NUM,NAME INTO :1,:2,:3'
        RETURNING BULK COLLECT INTO IDS,NUMS,NAMES;
        
    --输出集合数据
    K := IDS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== IDS ==');
    DBMS_OUTPUT.PUT_LINE(IDS.COUNT);
    WHILE K IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||IDS(K));
        K := IDS.NEXT(K);
    END LOOP;
    
    K := NUMS.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NUMS ==');
    DBMS_OUTPUT.PUT_LINE(NUMS.COUNT);
    WHILE K IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NUMS(K));
        K := NUMS.NEXT(K);
    END LOOP;
    
    K := NAMES.FIRST;
    DBMS_OUTPUT.PUT_LINE('== NAMES ==');
    DBMS_OUTPUT.PUT_LINE(NAMES.COUNT);
    WHILE K IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('DATA OF '|| K || ' IS '||NAMES(K));
        K := NAMES.NEXT(K);
    END LOOP;

END;
/

EXEC proc;
== IDS ==
5
DATA OF 1 IS 1
DATA OF 2 IS 2
DATA OF 3 IS 3
DATA OF 4 IS 4
DATA OF 5 IS 5
== NUMS ==
5
DATA OF 1 IS 100
DATA OF 2 IS 100
DATA OF 3 IS 100
DATA OF 4 IS 100
DATA OF 5 IS 100
== NAMES ==
5
DATA OF 1 IS NEW
DATA OF 2 IS NEW
DATA OF 3 IS NEW
DATA OF 4 IS NEW
DATA OF 5 IS NEW


SELECT * FROM tab ORDER BY NAME;
ID(int)      |NUM(int)      |NAME(varchar)      |
----------------------------------------------
1            |100           |NEW                |
----------------------------------------------
2            |100           |NEW                |
----------------------------------------------
3            |100           |NEW                |
----------------------------------------------
4            |100           |NEW                |
----------------------------------------------
5            |100           |NEW                |
总数目：5

--删除
DROP PROCEDURE proc;
DROP TABLE tab;