Oracle 10g 批量綁定 FORALL/BULK COLLECT 實戰：從原理到效能倍增

批量綁定核心價值

減少 PL/SQL 引擎與 SQL 引擎間的 **Context Switch（上下文切換）**，將成百上千次單獨 SQL 呼叫合併為一次批次操作，大幅降低 CPU 開銷與網路往返延遲。

兩大核心構件

Oracle 10g FORALL 三大迭代模式

-- 1. 數值範圍（傳統）
FORALL i IN 1..500000

-- 2. INDICES OF 集合（稀疏集合，取得下標值）
FORALL i IN INDICES OF sparse_coll

-- 3. VALUES OF 集合（取得集合元素值本身）
FORALL i IN VALUES OF index_coll

支援 **部分集合元素** 處理，SQL%BULK_ROWCOUNT(i) 回傳第 i 個元素影響的行數。

實戰效能對比：FOR 迴圈 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;
  tab_num t_num; tab_name t_name;
  t1 NUMBER; t2 NUMBER; t3 NUMBER;
BEGIN
  -- 準備 50 萬筆測試資料
  FOR i IN 1..500000 LOOP
    tab_num(i) := i;
    tab_name(i) := 'name: ' || i;
  END LOOP;

  SELECT dbms_utility.get_time INTO t1 FROM dual;
  FOR i IN 1..500000 LOOP
    INSERT INTO blktest VALUES (tab_num(i), tab_name(i));
  END LOOP;
  SELECT dbms_utility.get_time INTO t2 FROM dual;

  FORALL i IN 1..500000
    INSERT INTO blktest VALUES (tab_num(i), tab_name(i));
  SELECT dbms_utility.get_time INTO t3 FROM dual;

  dbms_output.put_line('FOR loop: '  || (t2-t1)/100 || ' 秒');
  dbms_output.put_line('FORALL:   '  || (t3-t2)/100 || ' 秒');
END;
-- 典型輸出：
-- FOR loop: 32.78 秒
-- FORALL:   2.64 秒  （提升 12 倍以上）

BULK COLLECT 多元用法

-- 1. SELECT INTO 批量查詢
SELECT * BULK COLLECT INTO tab_emp FROM scott.emp;

-- 2. FETCH INTO 遊標批量抓取
OPEN c; FETCH c BULK COLLECT INTO tab_emp; CLOSE c;

-- 3. DML RETURNING 批量回傳
DELETE FROM scott.emp RETURNING ename BULK COLLECT INTO tab_ename;

進階組合：FORALL + BULK COLLECT RETURNING

DECLARE
  TYPE t_num IS TABLE OF NUMBER;
  tab_in t_num := t_num(1,2,3);
  tab_out t_num;
BEGIN
  FORALL i IN 1..tab_in.COUNT
    UPDATE t2 SET id2 = id*2 WHERE id = tab_in(i)
    RETURNING id2 BULK COLLECT INTO tab_out;

  FOR i IN 1..tab_out.COUNT LOOP
    dbms_output.put_line(tab_out(i));
  END LOOP;
END;

避坑指南與最佳實踐

1. 集合記憶體：大量資料分批處理（如 `LIMIT 10000`），避免 PGA 爆滿。
2. 例外處理：`FORALL ... SAVE EXCEPTIONS` 配合 `SQL%BULK_EXCEPTIONS` 捕獲部分失敗，繼續處理其餘行。
3. 游標屬性：`SQL%ROWCOUNT` 返回總影響行數，`SQL%BULK_ROWCOUNT` 為陣列對應每元素行數。
4. 版本相容：10g 引入 `INDICES OF`/`VALUES OF`，9i 僅支援數值範圍。

結語

FORALL/BULK COLLECT 是 PL/SQL 高效能開發的「雙子星」。實務中將逐行迴圈重構為批次操作，配合分塊提交與例外隔離，可將大量資料處理時間從小時級壓縮至分鐘級。