FORALL语句实例

下面的实例，利用FORALL语句对tab表进行批量的更新操作

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

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;
    IDS IDTAB;
    NUMS NUMTAB;
    NAMES NAMETAB;
    ITERATIONS CONSTANT PLS_INTEGER := 5;
    K INT;
BEGIN
    FORALL II IN 1 .. ITERATIONS
    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 ==
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 11
DATA OF 2 IS 22
DATA OF 3 IS 33
DATA OF 4 IS 44
DATA OF 5 IS 55
== 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            |11            |NEW                |
----------------------------------------------
2            |22            |NEW                |
----------------------------------------------
3            |33            |NEW                |
----------------------------------------------
4            |44            |NEW                |
----------------------------------------------
5            |55            |NEW                |
总数目：5
--删除
DROP PROCEDURE proc;
DROP TABLE tab CASCADE;