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;
| 方式 | 10萬筆測試耗時 | Context Switch |
|---|---|---|
| FOR 迴圈逐筆 INSERT | ~45 秒 | 100,000 次 |
| FORALL 連續陣列 | ~1.2 秒 | 1 次 |
| FORALL INDICES OF (稀疏) | ~1.3 秒 | 1 次 |
| FORALL VALUES OF (自訂序) | ~1.3 秒 | 1 次 |