Oracle PL/SQL 游標完整教學：從入門到進階應用

什麼是游標

游標是指向查詢結果集中某一行的指標。有了游標，程式可逐行處理結果集：定位 → 取值 → 處理 → 移動下一行。

游標分類

• 隱式游標：DML（INSERT/UPDATE/DELETE）與 SELECT INTO 自動建立，屬性 SQL%ROWCOUNT、SQL%FOUND、SQL%NOTFOUND。
• 顯式游標：開發者宣告、控制的查詢游標，適用多行結果集處理。

顯式游標四步驟

1. 宣告：CURSOR cur_name (param type) IS SELECT ...;
2. 開啟：OPEN cur_name (arg);
3. 擷取：FETCH cur_name INTO var1, var2 ...;
4. 關閉：CLOSE cur_name;

游標屬性

基礎範例：手動控制迴圈

SET SERVEROUTPUT ON
DECLARE
  v_empno  VARCHAR2(20);
  v_empzc  VARCHAR2(20);
  CURSOR c_emp (p_comno NUMBER) IS
    SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = p_comno;
BEGIN
  IF NOT c_emp%ISOPEN THEN OPEN c_emp(627); END IF;
  FETCH c_emp INTO v_empno, v_empzc;
  WHILE c_emp%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_empno || ',' || v_empzc);
    EXIT WHEN c_emp%ROWCOUNT = 2;  -- 僅示範前 2 筆
    FETCH c_emp INTO v_empno, v_empzc;
  END LOOP;
  CLOSE c_emp;
END;
/

進階範例：搭配 RECORD 型別

SET SERVEROUTPUT ON
DECLARE
  TYPE r_emp IS RECORD (
    empno  cus_emp_basic.emp_no%TYPE,
    empzc  cus_emp_basic.emp_zc%TYPE
  );
  v_rec  r_emp;
  CURSOR c_emp (p_comno NUMBER) IS
    SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = p_comno;
BEGIN
  IF NOT c_emp%ISOPEN THEN OPEN c_emp(627); END IF;
  LOOP
    FETCH c_emp INTO v_rec;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('員工編號:' || v_rec.empno || ', 地址:' || v_rec.empzc);
  END LOOP;
  CLOSE c_emp;
END;
/

最佳寫法：CURSOR FOR LOOP（自動開啟/擷取/關閉）

SET SERVEROUTPUT ON
DECLARE
  CURSOR c_emp (p_comno NUMBER) IS
    SELECT emp_no, emp_zc FROM cus_emp_basic WHERE com_no = p_comno;
BEGIN
  FOR r IN c_emp(627) LOOP
    DBMS_OUTPUT.PUT_LINE('員工編號:' || r.emp_no || ', 地址:' || r.emp_zc);
  END LOOP;
  -- 迴圈結束自動 CLOSE，不可在迴圈內再用 OPEN/FETCH/CLOSE
END;
/

參數化游標與變數作用域

• 參數僅在 OPEN 或 FOR 時綁定，游標本體可重複開啟不同參數值。
• 游標宣告於 DECLARE 區塊，作用域限於該區塊；套件規格中宣告則為公共游標。

REF CURSOR：動態游標與跨層傳遞

TYPE t_refcur IS REF CURSOR;
v_cur t_refcur;
OPEN v_cur FOR SELECT * FROM employees WHERE department_id = :dept_id;
-- 可傳給 Java/.NET 客戶端、或另一 PL/SQL 程式處理

效能與最佳實踐

1. 優先使用 CURSOR FOR LOOP，程式碼最簡潔、不易漏關閉。
2. 大量資料處理改用 BULK COLLECT ... LIMIT 1000 + FORALL，減少 Context Switch。
3. 游標查詢務必加上適當 WHERE 條件，避免全表掃描後再篩選。
4. 長時間運行的游標考慮加上 FOR UPDATE SKIP LOCKED 避免阻塞。
5. 例外處理區塊務必 CLOSE 游標，防止游標洩漏導致 ORA-01000。

常見錯誤與排查

游標是 PL/SQL 處理集合資料的核心機制，掌握其生命週期與屬性，是編寫健壯存儲過程的必修課。