Oracle游標FETCH提取機制深度解析與迴圈化最佳實踐

FETCH核心機制

FETCH是從游標結果集逐行提取資料的唯一指令。每次執行推進內部指標至下一行，將欄位值賦予目標變數。語法結構：

FETCH cursor_name INTO variable[, variable,...];

關鍵約束條件

• 一一對應原則：目標變數列表與SELECT列表在數量、順序、資料型別上完全一致
• 指標推進：每次FETCH移動至下一行，超出範圍不報錯但變數值不變
• 狀態檢查：必須配合%NOTFOUND或%FOUND判斷提取結果

手工提取 vs 迴圈化提取對比

❌ 手工重複FETCH（不推薦）

OPEN c_emp;
FETCH c_emp INTO v_ename, v_salary;  -- 第1行
DBMS_OUTPUT.PUT_LINE(...);
FETCH c_emp INTO v_ename, v_salary;  -- 第2行
DBMS_OUTPUT.PUT_LINE(...);
FETCH c_emp INTO v_ename, v_salary;  -- 第3行
DBMS_OUTPUT.PUT_LINE(...);
CLOSE c_emp;

缺點：程式碼冗長、行數固定、維護困難、易出錯。

✅ 迴圈化標準模式（推薦）

SET SERVEROUTPUT ON
DECLARE
  v_ename  EMP.ENAME%TYPE;
  v_salary EMP.SALARY%TYPE;
  CURSOR c_emp IS SELECT ename, salary FROM emp;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_ename, v_salary;
    EXIT WHEN c_emp%NOTFOUND;  -- 核心：自動適應任意行數
    DBMS_OUTPUT.PUT_LINE('Salary of Employee ' || v_ename || ' is ' || v_salary);
  END LOOP;
  CLOSE c_emp;
END;

優點：簡潔通用、自動適應結果集大小、可讀性強、易於擴展業務邏輯。

進階FETCH模式

1. 批量提取：BULK COLLECT

適用於大量資料（>1000行），大幅減少SQL/PLSQL引擎切換：

DECLARE
  TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;
  v_emp_tab emp_tab_type;
  CURSOR c_emp IS SELECT * FROM emp;
BEGIN
  OPEN c_emp;
  FETCH c_emp BULK COLLECT INTO v_emp_tab LIMIT 100;  -- 分批提取
  FOR i IN 1..v_emp_tab.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_tab(i).ename);
  END LOOP;
  CLOSE c_emp;
END;

2. 游標FOR迴圈（隱式管理）

Oracle自動處理OPEN/FETCH/CLOSE，最簡潔：

BEGIN
  FOR rec IN (SELECT ename, salary FROM emp) LOOP
    DBMS_OUTPUT.PUT_LINE('Salary of Employee ' || rec.ename || ' is ' || rec.salary);
  END LOOP;
END;

注意：無法控制提取過程、無法使用%ROWCOUNT等屬性、不適用於需FOR UPDATE的場景。

效能優化建議

常見錯誤排查

• VALUE_ERROR： FETCH目標變數型別與SELECT列表不匹配（如NUMBER接VARCHAR2）
• INVALID_CURSOR： 對未OPEN或已CLOSE的游標執行FETCH
• 邏輯錯誤： 未檢查%NOTFOUND直接使用變數，處理到上一行殘留值
• 效能問題： 大數據量使用單行FETCH，應改用BULK COLLECT

總結

FETCH是游標操作的核心，掌握「單行提取→迴圈化→批量提取」三層演進，根據資料量與業務複雜度選擇適當模式。實務中建議：預設使用游標FOR迴圈，需精細控制時用顯式游標，大數據量必須上BULK COLLECT。