Oracle FORALL 批量 DML 完整指南：從基礎到 10g INDICES OF/VALUES OF 進階用法

Oracle FORALL 批量 DML 完整指南：從基礎到 10g INDICES OF/VALUES OF 進階用法

FORALL 是 PL/SQL 批量處理的核心語句，能將集合內容一次性傳遞給 SQL 引擎執行 INSERT/UPDATE/DELETE，大幅減少 Context Switch。本文從基礎語法、避開稀疏集合陷阱、到 10g 引入的 INDICES OF/VALUES OF 進階子句，附完整效能對比數據。

基礎語法

FORALL idx IN collection.FIRST .. collection.LAST
  INSERT INTO t VALUES collection(idx);

• idx 為隱含宣告的整數變數
• 範圍必須連續且已定義，否則拋 ORA-22160: element at index [N] does not exist

稀疏集合問題與 10g 解決方案

若集合有間隙（如刪除部分元素），傳統 FIRST..LAST 會報錯。Oracle 10g 引入兩個子句：

INDICES OF：針對稀疏集合

FORALL idx IN INDICES OF sparse_collection
  INSERT INTO t VALUES sparse_collection(idx);

自動跳過未定義索引，僅處理存在的元素。

VALUES OF：針對索引指標陣列

-- pointer_array 儲存要處理的元素索引
FORALL idx IN VALUES OF pointer_array
  INSERT INTO t VALUES binding_array(idx);

靈活指定任意子集，無需複製資料到新集合。

實戰案例：員工加薪與審計記錄

CREATE OR REPLACE PROCEDURE GIVE_RAISES (
  DEPT_IN IN EMPLOYEE.DEPARTMENT_ID%TYPE,
  NEWSAL  IN EMPLOYEE.SALARY%TYPE
) AS
  TYPE ID_AAT IS TABLE OF EMPLOYEE.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  TYPE SAL_AAT IS TABLE OF EMPLOYEE.SALARY%TYPE INDEX BY PLS_INTEGER;
  TYPE HDATE_AAT IS TABLE OF EMPLOYEE.HIRE_DATE%TYPE INDEX BY PLS_INTEGER;
  
  EMP_IDS     ID_AAT;
  SALARIES    SAL_AAT;
  HIRE_DATES  HDATE_AAT;
  
  APPROVED_IDS ID_AAT;   -- 符合條件的索引集合
  DENIED_IDS   ID_AAT;   -- 不符合條件的索引集合
BEGIN
  -- 1. 批量查詢
  SELECT EMPLOYEE_ID, SALARY, HIRE_DATE
  BULK COLLECT INTO EMP_IDS, SALARIES, HIRE_DATES
  FROM EMPLOYEE WHERE DEPARTMENT_ID = DEPT_IN;
  
  -- 2. 記憶體中分組（無資料複製，僅記錄索引）
  FOR I IN EMP_IDS.FIRST .. EMP_IDS.LAST LOOP
    IF COMP_ANALYSIS.IS_ELIGIBLE(EMP_IDS(I)) THEN
      APPROVED_IDS(I) := EMP_IDS(I);  -- 索引對齊
    ELSE
      DENIED_IDS(I) := EMP_IDS(I);
    END IF;
  END LOOP;
  
  -- 3. 批量寫入審計表（不合格員工）
  FORALL IDX IN VALUES OF DENIED_IDS
    INSERT INTO EMPLOYEE_HISTORY (EMPLOYEE_ID, SALARY, HIRE_DATE, ACTIVITY)
    VALUES (EMP_IDS(IDX), SALARIES(IDX), HIRE_DATES(IDX), 'RAISE DENIED');
  
  -- 4. 批量更新薪資（合格員工）
  FORALL IDX IN VALUES OF APPROVED_IDS
    UPDATE EMPLOYEE SET SALARY = NEWSAL WHERE EMPLOYEE_ID = EMP_IDS(IDX);
END;
/

效能對比數據（5萬/10萬筆員工）

結論：批量處理比逐行快 6-10 倍；VALUES OF 避免集合複製再提升 2-3 倍。

關鍵最佳實踐

1. 搭配 BULK COLLECT：SELECT ... BULK COLLECT INTO coll 一次性取回資料
2. 記憶體分組用索引而非複製資料：如範例用 APPROVED_IDS(I) := EMP_IDS(I) 僅複製索引值
3. FORALL 內不得有 COMMIT：整個 FORALL 為單一 SQL 執行，隱含原子性
4. SAVE EXCEPTIONS：FORALL ... SAVE EXCEPTIONS 允許部分失敗繼續執行，事後查 SQL%BULK_EXCEPTIONS
5. 集合型別選擇：關聯陣列（INDEX BY PLS_INTEGER）效能最佳，嵌套表/Varray 需初始化

掌握 FORALL + BULK COLLECT + VALUES OF 黃金組合，是 PL/SLC 高效能批量處理的標準姿勢。