萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 10g FORALL 增強:INDICES OF 與 VALUES OF 靈活處理稀疏陣列與自訂順序

PL/SQL 迴圈逐筆執行 DML 會頻繁在 PL/SQL 與 SQL 引擎間切換,FORALL 可批次綁定陣列一次送入 SQL 引擎,大幅降低開銷。10g 前 FORALL 僅支援連續索引,10g 引入 INDICES OF 與 VALUES OF 解決稀疏陣列與自訂順序問題。

語法演進

-- 9i 僅支援連續範圍
FORALL i IN 1..v_cnt
  INSERT INTO t VALUES (v_arr(i));

-- 10g INDICES OF:處理稀疏陣列(可跳過刪除的索引)
FORALL i IN INDICES OF v_sparse_arr
  [BETWEEN low AND high]
  INSERT INTO t VALUES (v_sparse_arr(i));

-- 10g VALUES OF:依指定索引順序處理
FORALL i IN VALUES OF v_index_arr
  INSERT INTO t VALUES (v_main_arr(i));

實戰範例:依條件篩選部門並批次插入

DECLARE
  TYPE dept_tab IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER;
  v_depts  dept_tab;
  TYPE idx_tab IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  v_idx    idx_tab;
  v_cnt    PLS_INTEGER := 0;
BEGIN
  -- 1. 批次讀取所有部門到陣列
  SELECT * BULK COLLECT INTO v_depts FROM departments;

  -- 2. 篩選 IT 相關部門,記錄其陣列索引
  FOR i IN 1..v_depts.COUNT LOOP
    IF v_depts(i).department_name LIKE '%IT%' THEN
      v_cnt := v_cnt + 1;
      v_idx(v_cnt) := i;  -- 保存原始索引,順序即為篩選順序
    END IF;
  END LOOP;

  -- 3. VALUES OF 依自訂順序批次插入
  FORALL i IN VALUES OF v_idx
    INSERT INTO it_departments VALUES v_depts(i);

  COMMIT;
END;

效能對比

適用場景

  • INDICES OF:陣列中間有刪除/未初始化元素(如 v_arr.DELETE(5)),避免 ORA-22160 錯誤。
  • VALUES OF:需依特定業務順序處理(如優先處理 VIP 客戶),或從多來源合併索引。
  • 搭配 SAVE EXCEPTIONS 處理部分失敗:FORALL ... SAVE EXCEPTIONS,事後檢查 SQL%BULK_EXCEPTIONS。

限制與注意

  • 陣列型別必須為集合型別(Nested Table、VARRAY、Associative Array),不可為標量變數。
  • VALUES OF 索引陣列必須為 PLS_INTEGER 或 BINARY_INTEGER 型別。
  • 12c+ 可直接在 SQL 中使用 FORALL 搭配 EXECUTE IMMEDIATE 動態 SQL。

善用 INDICES OF、VALUES OF 釋放 FORALL 完整威力,讓批次 DML 既靈活又高效。

copyright © 萬盛學電腦網 all rights reserved
方式10萬筆測試耗時Context Switch
FOR 迴圈逐筆 INSERT~45 秒100,000 次
FORALL 連續陣列~1.2 秒1 次
FORALL INDICES OF (稀疏)~1.3 秒1 次
FORALL VALUES OF (自訂序)~1.3 秒1 次