Oracle 批量 FETCH 進階技巧：集合與 %ROWTYPE 結合簡化代碼

基本 BULK COLLECT INTO 模式

將游標一次性抓取多行至 PL/SQL 集合，減少 Context Switch，大幅提升批量處理效能。

寫法演進：從逐欄位集合到 %ROWTYPE 集合

階段 1：每欄位一個集合（繁瑣）

DECLARE
  CURSOR c1 IS SELECT * FROM t_depart;
  TYPE code_t IS TABLE OF t_depart.depart_code%TYPE;
  TYPE name_t IS TABLE OF t_depart.depart_name%TYPE;
  v_code code_t; v_name name_t;
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;

階段 2：單一 %ROWTYPE 集合（推薦）

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

優勢： 結構對應表定義，增減欄位無需修改集合宣告，代碼極度精簡。

階段 3：搭配 FIRST..LAST 遍歷稀疏集合

FOR i IN v_dept.FIRST .. v_dept.LAST LOOP
  IF v_dept.EXISTS(i) THEN  -- 跳過已刪除元素
    DBMS_OUTPUT.PUT_LINE(v_dept(i).depart_code);
  END IF;
END LOOP;

適用於中間可能 `DELETE(v_dept(i))` 導致稀疏的場景。

進階：LIMIT 子句分批抓取（防止 PGA 爆滿）

DECLARE
  CURSOR c1 IS SELECT * FROM huge_table;
  TYPE dept_t IS TABLE OF huge_table%ROWTYPE;
  v_dept dept_t;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO v_dept LIMIT 1000;  -- 每次 1000 行
    EXIT WHEN v_dept.COUNT = 0;
    FORALL i IN 1..v_dept.COUNT
      INSERT INTO target_table VALUES v_dept(i);  -- 批量 DML
    COMMIT;
  END LOOP;
  CLOSE c1;
END;

LIMIT 控制單次抓取行數，配合 `FORALL` 實現「分批抓取、批量寫入」流水線，記憶體佔用恆定，適合百萬級資料遷移/ETL。

常見錯誤與避坑指南

總結

BULK COLLECT INTO ... %ROWTYPE + LIMIT + FORALL 是高效 PL/SQL 批量處理的黃金組合。掌握集合遍歷語義（COUNT vs FIRST..LAST vs INDICES OF），即可寫出健壯、高效能的批量作業代碼。