Oracle Bulk collect 和 forall语法

概述：　　

　　在处理一个大约有400W数据的表中的冗余数据，使用cursor来一条一条处理的时候，发现耗时大约为5 hours左右。之后采用Oracle提供的BULK COLLECT和FORALL改写程序，耗时降到1个小时左右，速度提高了5倍左右。原因是：

1. 使用BLUK COLLECT一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下。但BLUK COLLECT需要大量内存。
2. 使用FORALL比FOR效率高，因为前者只切换一次上下文，而后者将是在循环次数一样多个上下文间切换。

语法：

1. select into中使用bulk collect

　SELECT id, code BULK COLLECT INTO v_table;

2. fetch into中使用bulk collect

    fetch cursor1 BULK COLLECT INTO v_table;

3. returning into中使用bulk collect
    DELETE FROM table1 WHERE id = 1 RETURNING id, code BULK COLLECT INTO v_table;
4. 批量更新中，使用forall而不是FOR 

DECLARE
    TYPE NumList IS VARRAY(20) OF NUMBER;
　　depts NumList := NumList(10, 30, 70);
BEGIN
　　FOR i IN depts.FIRST .. depts.LAST LOOP
　　   UPDATE t_test SET sal = sal * 1.10 WHERE deptno = depts(i);
　　END LOOP; 
END;

改用FORALL后：

DECLARE
    TYPE NumList IS VARRAY(20) OF NUMBER;
    depts NumList := NumList(10, 30, 70);
BEGIN
    FORALL i IN depts.FIRST..depts.LAST 
        UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i); 
END; 

完整语法示例：

declare
  TYPE REL IS RECORD(
    id      T_REL.id%TYPE,
    code    T_REL.code%TYPE,
   );

  TYPE TABLE_REL IS TABLE OF REL INDEX BY BINARY_INTEGER;
  var_rel TABLE_REL;

 cursor cur_MAX_REL is
    SELECT max(id) as id
      FROM T_REL
     GROUP BY code;         
  
begin
  open cur_MAX_REL;
  loop
    FETCH cur_MAX_REL BULK COLLECT
      INTO var_rel LIMIT 10000;
      
    FORALL i IN var_rel.FIRST .. var_rel.LAST
      insert into t_test
        (id, code)
        values var_rel(i);

    exit when cur_MAX_REL%NOTFOUND;
  end loop;
  close cur_MAX_REL;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    IF cur_MAX_REL%ISOPEN THEN
      CLOSE cur_MAX_REL;
    END IF;
    dbms_output.put_line(chr(10) || sqlerrm);
END;