VALUES OF语句实例

如下实例使用VALUES OF语法进行批量执行。参数集合INDICE的全部有效元素值为1、3、5，因此相应FORALL的循环变量分别取值1、3、5，对表中相应数据进行更新操作。

--  清理环境
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');

SELECT * FROM tab ORDER BY NAME;
ID(int)      |NUM(int)      |NAME(varchar)      |
----------------------------------------------
1            |11            |1A                 |
----------------------------------------------
2            |22            |2B                 |
----------------------------------------------
3            |33            |3C                 |
----------------------------------------------
4            |44            |4D                 |
----------------------------------------------
5            |55            |5E                 |
总数目：5

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;
    TYPE INDICESTAB IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
    IDS IDTAB;
    NUMS NUMTAB;
    NAMES NAMETAB;
    INDICE INDICESTAB;
    ITERATIONS CONSTANT PLS_INTEGER := 5;
    K INT;
BEGIN
    INDICE(100) := 1;
    INDICE(300) := 3;
    INDICE(500) := 5;
    FORALL II IN VALUES OF INDICE
    UPDATE tab SET NAME = 'NEW' WHERE ID = II RETURNING ID,NUM,NAME 
        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 ==
3
DATA OF 1 IS 1
DATA OF 2 IS 3
DATA OF 3 IS 5
== NUMS ==
3
DATA OF 1 IS 11
DATA OF 2 IS 33
DATA OF 3 IS 55
== NAMES ==
3
DATA OF 1 IS NEW
DATA OF 2 IS NEW
DATA OF 3 IS NEW


SELECT * FROM tab ORDER BY NAME;
ID(int)      |NUM(int)      |NAME(varchar)      |
----------------------------------------------
2            |22            |2B                 |
----------------------------------------------
4            |44            |4D                 |
----------------------------------------------
1            |11            |NEW                |
----------------------------------------------
3            |33            |NEW                |
----------------------------------------------
5            |55            |NEW                |
总数目：5

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