Oracle BULK COLLECT 批量處理完全指南：從基礎到 %ROWTYPE 進階用法

為什麼需要 BULK COLLECT？

在 PL/SQL 中逐筆 FETCH 游標資料會產生大量 Context Switch（SQL 引擎與 PL/SQL 引擎切換），嚴重影響效能。BULK COLLECT 可一次將查詢結果批量載入集合，大幅減少切換開銷，是處理大量資料的關鍵技術。

基礎用法：逐欄位定義集合

DECLARE
  CURSOR c1 IS SELECT * FROM t_depart;
  TYPE v_code_type IS TABLE OF t_depart.depart_code%TYPE;
  TYPE v_name_type IS TABLE OF t_depart.depart_name%TYPE;
  v_code v_code_type;
  v_name v_name_type;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO v_code, v_name;
  FOR i IN 1..v_code.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_code(i) || ' ' || v_name(i));
  END LOOP;
  CLOSE c1;
END;

缺點：欄位多時需定義多個集合，程式碼冗長。

進階用法：搭配 %ROWTYPE 簡化程式

DECLARE
  CURSOR c1 IS SELECT * FROM t_depart;
  TYPE v_depart_type IS TABLE OF t_depart%ROWTYPE;
  v_depart v_depart_type;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO v_depart;
  FOR i IN 1..v_depart.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_depart(i).depart_code || ' ' || v_depart(i).depart_name);
  END LOOP;
  CLOSE c1;
END;

優點：單一集合即可承載整列資料，程式碼簡潔且易維護。

集合遍歷的三種寫法

1. 數字索引迴圈：FOR i IN 1..v_depart.COUNT LOOP
2. FIRST/LAST 迴圈：FOR i IN v_depart.FIRST..v_depart.LAST LOOP（適用稀疏集合）
3. WHILE 迴圈：搭配 v_depart.NEXT(i) 遍歷稀疏集合

關鍵注意事項

• 記憶體用量：BULK COLLECT 會將所有資料載入 PGA，大量資料時建議搭配 LIMIT 子句分批處理。
• 正確引用欄位：集合元素為記錄型別時，語法為 v_depart(i).depart_code，而非 v_depart.depart_code(i)。
• 隱含游標也可用：SELECT * BULK COLLECT INTO v_depart FROM t_depart; 可省去 OPEN/FETCH/CLOSE。

分批處理範本（生產環境建議）

DECLARE
  CURSOR c1 IS SELECT * FROM t_depart;
  TYPE v_depart_type IS TABLE OF t_depart%ROWTYPE;
  v_depart v_depart_type;
  v_limit CONSTANT PLS_INTEGER := 1000;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO v_depart LIMIT v_limit;
    EXIT WHEN v_depart.COUNT = 0;
    FORALL i IN 1..v_depart.COUNT
      -- 批量 DML 操作，如 INSERT/UPDATE/DELETE
      NULL; 
  END LOOP;
  CLOSE c1;
END;

總結

BULK COLLECT 配合 %ROWTYPE 集合，是 PL/SQL 高效處理大量資料的標準作法。善用 LIMIT 避免記憶體溢位，搭配 FORALL 進行批量 DML，可將效能提升數十倍以上。