Oracle 游標管理全攻略：從隱式到 REF CURSOR 與動態 SQL

前言

游標是 PL/SQL 處理多行查詢結果的核心機制。本文系統梳理隱式游標屬性、顯式游標完整生命週期、參數化游標、FOR 迴圈簡化寫法、REF CURSOR 動態綁定與動態 SQL 進階應用，並提供可直接執行的範例碼。

一、隱式游標屬性：SQL%FOUND / %ROWCOUNT / %ISOPEN

Oracle 為每執行一條 DML（INSERT/UPDATE/DELETE）或 SELECT INTO 語句自動建立隱式游標，名為 SQL，僅存在於該語句執行期間。

1.1 %FOUND：判斷是否影響資料

BEGIN
  UPDATE employees2 SET first_name = first_name || ' t' WHERE employee_id = 2;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('資料已更新');
  ELSE
    DBMS_OUTPUT.PUT_LINE('查無資料');
  END IF;
END;
/

1.2 %ROWCOUNT：取得影響筆數

BEGIN
  UPDATE employees2 SET first_name = 'john' WHERE employee_id = 2;
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('更新了 ' || SQL%ROWCOUNT || ' 筆');
  END IF;
END;
/

1.3 %ISOPEN：隱式游標永遠為 FALSE

Oracle 執行完 SQL 即自動關閉隱式游標，因此 SQL%ISOPEN 永遠回傳 FALSE，僅適用於顯式游標檢查。

二、顯式游標：完整控制查詢流程

顯式游標需經歷 DECLARE → OPEN → FETCH → CLOSE 四階段，適用於需逐行處理邏輯的場景。

2.1 基本範例：查詢 division_id='SUP' 的員工

DECLARE
  CURSOR emp_cur IS
    SELECT * FROM employees2 WHERE division_id = 'SUP';
  v_rec employees2%ROWTYPE;
BEGIN
  OPEN emp_cur;
  FETCH emp_cur INTO v_rec;
  WHILE emp_cur%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('ID=' || v_rec.employee_id ||
                         ' Div=' || v_rec.division_id ||
                         ' Name=' || v_rec.first_name);
    FETCH emp_cur INTO v_rec;
  END LOOP;
  CLOSE emp_cur;
END;
/

2.2 使用 %NOTFOUND 替代 %FOUND 簡化邏輯

DECLARE
  CURSOR emp_cur IS SELECT first_name FROM employees2 WHERE division_id = 'SUP';
  v_name employees2.first_name%TYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Row ' || emp_cur%ROWCOUNT || ': ' || v_name);
  END LOOP;
  CLOSE emp_cur;
END;
/

2.3 %ISOPEN 檢查游標狀態

DECLARE
  CURSOR c_emp (p_id NUMBER) IS
    SELECT first_name FROM employees2 WHERE employee_id = p_id;
  v_name employees2.first_name%TYPE;
BEGIN
  IF c_emp%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('游標已開啟');
  ELSE
    OPEN c_emp(2);
    FETCH c_emp INTO v_name;
    DBMS_OUTPUT.PUT_LINE(v_name);
    CLOSE c_emp;
  END IF;
END;
/

三、FOR 迴圈游標：隱式開啟/提取/關閉

Oracle 9i 後支援 FOR record IN cursor_name LOOP，自動處理 OPEN/FETCH/CLOSE，程式碼更簡潔。

3.1 基本 FOR 迴圈

DECLARE
  CURSOR c_emp (p_id NUMBER) IS
    SELECT first_name FROM employees2 WHERE employee_id = p_id;
BEGIN
  FOR rec IN c_emp(2) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.first_name);
  END LOOP;
END;
/

3.2 帶參數 FOR 迴圈：互動式輸入 division_id

SET SERVEROUTPUT ON;
DECLARE
  v_div_id VARCHAR2(5) := '&division_id'; -- SQL*Plus 替換變數
  CURSOR c_emp (p_div VARCHAR2) IS
    SELECT employee_id, division_id, job_id, first_name, last_name
      FROM employees2
     WHERE division_id = p_div;
BEGIN
  FOR rec IN c_emp(v_div_id) LOOP
    DBMS_OUTPUT.PUT_LINE('ID=' || rec.employee_id);
  END LOOP;
END;
/

四、REF CURSOR：動態查詢的關鍵

REF CURSOR 是指向游標的指標型別，允許在執行期動態決定查詢語句，常用於回傳結果集給應用層（Java/.NET/Python）或跨 PL/SQL 區塊傳遞游標。

4.1 強型別 vs 弱型別 REF CURSOR

• 強型別：TYPE t_cur IS REF CURSOR RETURN employees2%ROWTYPE; 限定回傳結構。
• 弱型別：TYPE t_cur IS REF CURSOR; 可指向任意查詢，彈性最高。

4.2 範例：依使用者輸入切換查詢員工或部門

SET SERVEROUTPUT ON;
ACCEPT tab PROMPT '查詢員工(E)或部門(D)：';

DECLARE
  TYPE ref_cur_t IS REF CURSOR; -- 弱型別
  v_cur   ref_cur_t;
  v_id    NUMBER;
  v_name  VARCHAR2(100);
  v_sel   VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1));
BEGIN
  IF v_sel = 'E' THEN
    OPEN v_cur FOR SELECT empno, ename FROM emp;
    DBMS_OUTPUT.PUT_LINE('=== 員工資訊 ===');
  ELSIF v_sel = 'D' THEN
    OPEN v_cur FOR SELECT deptno, dname FROM dept;
    DBMS_OUTPUT.PUT_LINE('=== 部門資訊 ===');
  ELSE
    DBMS_OUTPUT.PUT_LINE('請輸入 E 或 D');
    RETURN;
  END IF;

  LOOP
    FETCH v_cur INTO v_id, v_name;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('#' || v_id || ':' || v_name);
  END LOOP;
  CLOSE v_cur;
END;
/

4.3 動態 SQL 搭配 REF CURSOR：參數化排序與過濾

SET SERVEROUTPUT ON;
VARIABLE max_sal NUMBER;
EXECUTE :max_sal := 2500;

DECLARE
  TYPE c_type IS REF CURSOR;
  v_cur     c_type;
  v_rec     emp%ROWTYPE;
  v_sal     NUMBER := :max_sal;
BEGIN
  OPEN v_cur FOR
    'SELECT * FROM emp WHERE sal > :1 ORDER BY sal DESC'
    USING v_sal;

  DBMS_OUTPUT.PUT_LINE('薪水大於 ' || v_sal || ' 的員工：');
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('編號:' || v_rec.empno ||
                         ' 姓名:' || v_rec.ename ||
                         ' 薪水:' || v_rec.sal);
  END LOOP;
  CLOSE v_cur;
END;
/

五、FOR UPDATE NOWAIT / WAIT n：鎖定資料列

在游標查詢後加上 FOR UPDATE 可鎖定提取的資料列，防止併發修改衝突。

• FOR UPDATE NOWAIT：資源被鎖時立即報錯 (ORA-00054)。
• FOR UPDATE WAIT 5：最多等待 5 秒再報錯。
• FOR UPDATE SKIP LOCKED (11g+)：跳過已鎖定列，適合佇列處理。

DECLARE
  CURSOR c_lock IS
    SELECT * FROM employees2 WHERE employee_id = 2 FOR UPDATE WAIT 3;
  v_rec employees2%ROWTYPE;
BEGIN
  OPEN c_lock;
  FETCH c_lock INTO v_rec;
  -- 執行更新邏輯
  UPDATE employees2 SET salary = salary * 1.1 WHERE CURRENT OF c_lock;
  COMMIT;
  CLOSE c_lock;
END;
/

六、總結與選型建議

熟練掌握各類游標技術，能讓 PL/SQL 程式在效能、併發與維護性間取得最佳平衡。