Oracle PL/SQL 游標完整指南：宣告、開啟、擷取、關閉五步驟實務

前言

游標是 PL/SQL 處理多筆查詢結果的核心機制。本文詳細說明明確游標的五個標準步驟、屬性應用、FOR 迴圈簡化寫法、參數化游標與 REF CURSOR 進階用法，並提供完整範例程式碼。

一、明確游標五步驟標準流程

1. 宣告變數：對應 SELECT 清單每一欄位的資料型別 (建議用 table.column%TYPE 錨定)。
2. 宣告游標：定義 SELECT 陳述式 (可帶參數)。
3. 開啟游標：OPEN cursor_name [ (parameters) ]; 執行查詢、建立結果集指標。
4. 擷取資料：FETCH cursor_name INTO var1, var2, ...; 逐筆讀取至變數。
5. 關閉游標：CLOSE cursor_name; 釋放資源、游標變數可重複開啟。

二、完整範例：查詢 PERSON 表並列印

CREATE OR REPLACE PROCEDURE sel_person IS
    v_id       person.id%TYPE;
    v_name     person.pname%TYPE;
    v_birthday person.birthday%TYPE;

    CURSOR c_person IS
        SELECT id, pname, birthday FROM person;

BEGIN
    OPEN c_person;
    LOOP
        FETCH c_person INTO v_id, v_name, v_birthday;
        EXIT WHEN c_person%NOTFOUND;          -- 關鍵：判斷是否讀完
        DBMS_OUTPUT.PUT_LINE(v_id || '----' || v_name || '----' || v_birthday);
    END LOOP;
    CLOSE c_person;
END sel_person;
/

三、游標屬性 (隱性回饋資訊)

四、FOR 迴圈游標 (隱性游標) — 最推薦寫法

Oracle 自動處理 OPEN/FETCH/CLOSE，程式碼最精簡：

BEGIN
    FOR rec IN (SELECT id, pname, birthday FROM person) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.id || '----' || rec.pname || '----' || rec.birthday);
    END LOOP;
END;
/

• 適用於「單純遍歷查詢結果、無需複雜控制流程」的情境。
• 記錄變數 rec 為隱性宣告的 ROWTYPE，欄位名同查詢別名或表欄位名。

五、參數化游標 — 重複使用、提升安全性

CURSOR c_emp_by_dept (p_deptno NUMBER) IS
    SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;

BEGIN
    FOR rec IN c_emp_by_dept(10) LOOP   -- 傳入參數
        DBMS_OUTPUT.PUT_LINE(rec.empno || ' ' || rec.ename);
    END LOOP;
    -- 同一游標可再次開啟傳入不同參數
    FOR rec IN c_emp_by_dept(20) LOOP
        ...
    END LOOP;
END;

六、REF CURSOR (動態游標) — 介面傳遞結果集

TYPE t_ref_cursor IS REF CURSOR;          -- 弱型別 REF CURSOR
TYPE t_emp_ref    IS REF CURSOR RETURN emp%ROWTYPE;  -- 強型別

PROCEDURE get_emp_data (p_deptno IN NUMBER, p_rc OUT t_ref_cursor) IS
BEGIN
    OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;

應用場景：Java/.NET 呼叫 Stored Procedure 取得 ResultSet、動態 SQL 組裝查詢條件。

七、FOR UPDATE 與 WHERE CURRENT OF — 逐列鎖定更新

CURSOR c_emp_lock IS
    SELECT empno, sal FROM emp WHERE deptno = 20 FOR UPDATE OF sal;

BEGIN
    FOR rec IN c_emp_lock LOOP
        UPDATE emp SET sal = sal * 1.1
        WHERE CURRENT OF c_emp_lock;   -- 只更新目前游標指向的列
    END LOOP;
    COMMIT;
END;

• FOR UPDATE 於 OPEN 時對結果集加排他鎖 (可加 NOWAIT/WAIT n)。
• WHERE CURRENT OF 避免重複 WHERE 條件，確保更新正是當前列。

八、常見錯誤與除錯技巧

結語

熟練掌握游標五步驟、屬性判斷、FOR 迴圈簡化寫法與 REF CURSOR 進階應用，是編寫高品質 PL/SQL 程式的基本功。實務建議：優先使用 FOR 迴圈游標，僅在需精確控制開啟/關閉時機、跨程序傳遞結果集或逐列鎖定更新時，才使用明確游標。