Oracle 10g FORALL 增強：INDICES OF 與 VALUES OF 玩轉非連續陣列

背景：PL/SQL 迴圈執行 DML 的效能代價

每次迴圈切換 PL/SQL → SQL 引擎，上下文切換開銷大。`FORALL` 批次綁定陣列值一次性提交，大幅降低切換次數。

10g 前限制

FORALL i IN lower_bound .. upper_bound  -- 必須連續、不可跳過

嵌套表刪除中間元素後無法直接用 FORALL。

10g 兩大增強子句

INDICES OF：處理稀疏陣列

FORALL i IN INDICES OF sparse_collection
  [BETWEEN low AND high]  -- 可選範圍限制
  sql_statement;

自動略過未初始化索引，只處理現有元素。

VALUES OF：自訂處理順序與子集

-- 建立索引陣列，順序即執行順序
TYPE num_arr IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
idx_arr num_arr;
idx_arr(1) := 5;  -- 先處理第 5 筆
idx_arr(2) := 2;  -- 再處理第 2 筆
idx_arr(3) := 9;

FORALL i IN VALUES OF idx_arr
  sql_statement;

完整範例：HR 範本結構，只 INSERT IT 相關部門

DECLARE
  TYPE dept_tab IS TABLE OF departments%ROWTYPE;
  depts dept_tab;
  TYPE idx_tab IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  it_idx idx_tab;
  v_cnt PLS_INTEGER := 0;
BEGIN
  -- 1. 批次讀入所有部門
  SELECT * BULK COLLECT INTO depts FROM departments;

  -- 2. 篩選 IT 部門，記錄索引
  FOR i IN 1..depts.COUNT LOOP
    IF depts(i).department_name LIKE 'IT%' THEN
      v_cnt := v_cnt + 1;
      it_idx(v_cnt) := i;
    END IF;
  END LOOP;

  -- 3. FORALL + VALUES OF 批次 INSERT 到暫存表
  FORALL j IN VALUES OF it_idx
    INSERT INTO temp_it_depts VALUES depts(it_idx(j));

  DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' IT departments');
END;
/
-- 輸出
Inserted 3 IT departments

效能對比

適用場景

• 稀疏嵌套表（刪除中間元素後）。
• 需依業務邏輯順序處理（如優先級、依賴關係）。
• 只處理符合條件的子集（避免全量綁定再由 SQL 過濾）。

注意事項

• `VALUES OF` 索引陣列必須密集（1..N 無空洞），否則報錯。
• 11g+ 可搭配 `SAVE EXCEPTIONS` 處理部分失敗。
• 大量資料建議分批（如每 10,000 筆 COMMIT），避免 UNDO 爆滿。