Oracle 10g 批量綁定 FORALL / BULK COLLECT 實戰：性能提升 10 倍+

核心概念

• FORALL：批量 DML（INSERT/UPDATE/DELETE），減少 PL/SQL ↔ SQL 引擎上下文切換。
• BULK COLLECT：批量查詢（SELECT INTO / FETCH INTO / DML RETURNING），一次拉取大量行到集合。

FORALL 三種寫法（10g 新增）

-- 1. 範圍
FORALL i IN 1..500000 INSERT ...;

-- 2. INDICES OF（稀疏集合）
FORALL i IN INDICES OF sparse_collection INSERT ...;

-- 3. VALUES OF（值驅動）
FORALL i IN VALUES OF index_collection INSERT ...;

SQL%BULK_ROWCOUNT(i) 返回第 i 個 DML 影響行數。

實測對比：FOR LOOP vs FORALL

DECLARE
  TYPE t_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE t_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  nums t_num; names t_name;
  t1 NUMBER; t2 NUMBER; t3 NUMBER;
BEGIN
  FOR i IN 1..500000 LOOP nums(i):=i; names(i):='name: '||i; END LOOP;
  SELECT dbms_utility.get_time INTO t1 FROM dual;
  FOR i IN 1..500000 LOOP INSERT INTO blktest VALUES(nums(i), names(i)); END LOOP;
  SELECT dbms_utility.get_time INTO t2 FROM dual;
  FORALL i IN 1..500000 INSERT INTO blktest VALUES(nums(i), names(i));
  SELECT dbms_utility.get_time INTO t3 FROM dual;
  dbms_output.put_line('FOR loop: ' || (t2-t1)/100 || 's');
  dbms_output.put_line('FORALL:   ' || (t3-t2)/100 || 's');
END;
/*
FOR loop: 32.78s
FORALL:   2.64s  -- 快 12 倍！
*/

BULK COLLECT 經典用法

-- 1. SELECT INTO
SELECT * BULK COLLECT INTO emp_tab FROM scott.emp;

-- 2. FETCH INTO
OPEN c; FETCH c BULK COLLECT INTO emp_tab; CLOSE c;

-- 3. DML RETURNING
DELETE FROM scott.emp RETURNING ename BULK COLLECT INTO name_tab;

組合拳：FORALL + RETURNING BULK COLLECT

DECLARE
  TYPE t_num IS TABLE OF NUMBER; ids t_num := t_num(1,2,3);
  TYPE t_out IS TABLE OF NUMBER; outs t_out;
BEGIN
  FORALL i IN 1..ids.COUNT
    UPDATE t2 SET id2=id*2 WHERE id=ids(i) RETURNING id2 BULK COLLECT INTO outs;
  FOR i IN 1..outs.COUNT LOOP dbms_output.put_line(outs(i)); END LOOP;
END;

最佳實踐

• 批量大小控制在 100~1000，避免 PGA 爆炸（可用 LIMIT 分批）。
• 異常處理：FORALL ... SAVE EXCEPTIONS 配合 SQL%BULK_EXCEPTIONS 繼續執行並事後收集錯誤。
• 適用場景：大量數據導入/導出、ETL、報表快照、緩存預熱。

批量綁定是 PL/SQL 性能優化的「核武器」，善用之，讓上下文切換不再是瓶頸。