Oracle BULK COLLECT 與 FORALL 批量處理實戰：大幅提升 PL/SQL 大量數據增刪改性能

為什麼需要批量綁定

PL/SQL 與 SQL 引擎上下文切換是性能瓶頸。BULK COLLECT 批量查詢，FORALL 批量 DML，可將切換次數從 N 次降為 1 次，性能提升數量級。

FORALL 增強特性（10g+）

• INDICES OF：處理稀疏數組（含間隙），僅遍歷已定義索引。
• VALUES OF：指定索引數組，靈活選取子集執行 DML。

-- INDICES OF 示例
FORALL indx IN INDICES OF sparse_collection
  INSERT INTO my_table VALUES sparse_collection(indx);

-- VALUES OF 示例
FORALL indx IN VALUES OF pointer_array
  INSERT INTO my_table VALUES binding_array(indx);

實戰案例：部門員工加薪與記錄拒絕

傳統游標循環（耗時 30 分鐘）

FOR emp_rec IN (SELECT employee_id, salary, hire_date FROM employee WHERE department_id = dept_in) LOOP
  IF comp_analysis.is_eligible(emp_rec.employee_id) THEN
    UPDATE employee SET salary = newsal WHERE employee_id = emp_rec.employee_id;
  ELSE
    INSERT INTO employee_history ... VALUES (emp_rec.employee_id, emp_rec.salary, emp_rec.hire_date, 'RAISE DENIED');
  END IF;
END LOOP;

批量綁定重構（耗時 10 分鐘）

1. 定義集合類型：分別對應 employee_id、salary、hire_date 的關聯數組與嵌套表。
2. 批量查詢：SELECT ... BULK COLLECT INTO ... 一次性取出符合條件的所有員工信息。
3. 內存分區：循環遍歷集合，調用 is_eligible，將符合/不符合條件的員工分別放入 approved_* 與 denied_* 集合（保持稀疏索引）。
4. 批量 DML：

   FORALL indx IN INDICES OF denied_employee_ids
     INSERT INTO employee_history ... VALUES (denied_employee_ids(indx), denied_salaries(indx), denied_hire_dates(indx), 'RAISE DENIED');
   
   FORALL indx IN INDICES OF approved_employee_ids
     UPDATE employee SET salary = newsal WHERE employee_id = approved_employee_ids(indx);

關鍵點總結

• 集合定義盡量基於 %TYPE 或 %ROWTYPE，但 FORALL 不支持記錄集合直接操作，需拆分為標量集合。
• 稀疏數組配合 INDICES OF 避免 ORA-22160 異常。
• 批量處理雖增加代碼量，但對大數據量場景性能提升顯著，是 PL/SQL 高性能開發的必備技能。