Oracle 游標 vs 游標變數完整對比：靜態與動態、強弱型別、傳遞機制深度解析

核心差異一覽表

游標變數型別定義

1. 弱型別 REF CURSOR（通用、彈性）

-- 系統內建弱型別
SYS_REFCURSOR  -- 等同於 TYPE REF CURSOR;

-- 自訂弱型別
TYPE weak_refcur IS REF CURSOR;

特點：可關聯任意結構的查詢，編譯期不檢查結構匹配，執行期才驗證。

2. 強型別 REF CURSOR（型別安全）

-- 基於表/視圖列定義
TYPE strong_refcur IS REF CURSOR RETURN employees%ROWTYPE;

-- 基於自訂 RECORD 型別
TYPE emp_rec_type IS RECORD (
  emp_id NUMBER, emp_name VARCHAR2(100), dept_name VARCHAR2(50)
);
TYPE strong_refcur2 IS REF CURSOR RETURN emp_rec_type;

特點：編譯期檢查查詢結構與 RETURN 型別匹配，防止執行期結構不符錯誤。

宣告與操作語法對比

實戰範例 1：動態查詢建構

CREATE OR REPLACE PROCEDURE query_by_dept(p_dept_id NUMBER, p_result OUT SYS_REFCURSOR) IS
  v_sql VARCHAR2(1000);
BEGIN
  -- 動態建構查詢（實務中防 SQL 注入需用 DBMS_ASSERT）
  v_sql := 'SELECT employee_id, first_name, salary FROM employees WHERE department_id = :b1';
  
  OPEN p_result FOR v_sql USING p_dept_id;
END;
/

-- 呼叫端
DECLARE
  v_cur SYS_REFCURSOR;
  v_rec employees%ROWTYPE;
BEGIN
  query_by_dept(30, v_cur);
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.employee_id || ': ' || v_rec.first_name);
  END LOOP;
  CLOSE v_cur;
END;
/

實戰範例 2：同一游標變數重用不同查詢

DECLARE
  v_cur SYS_REFCURSOR;
  v_rec1 employees%ROWTYPE;
  v_rec2 departments%ROWTYPE;
BEGIN
  -- 第一次：查員工
  OPEN v_cur FOR SELECT * FROM employees WHERE department_id = 10;
  FETCH v_cur INTO v_rec1;
  CLOSE v_cur;

  -- 第二次：查部門（同一變數，不同結構）
  OPEN v_cur FOR SELECT * FROM departments WHERE department_id = 10;
  FETCH v_cur INTO v_rec2;
  CLOSE v_cur;
END;
/
-- 僅限弱型別；強型別會編譯錯誤

實戰範例 3：作為函數回傳值

CREATE OR REPLACE FUNCTION get_emp_cursor(p_dept_id NUMBER) RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR 
    SELECT employee_id, first_name, last_name, salary 
    FROM employees WHERE department_id = p_dept_id;
  RETURN v_cur;  -- 呼叫端負責 CLOSE
END;
/

-- 使用
DECLARE
  v_cur SYS_REFCURSOR;
  v_rec employees%ROWTYPE;
BEGIN
  v_cur := get_emp_cursor(30);
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.first_name);
  END LOOP;
  CLOSE v_cur;
END;
/

實戰範例 4：強型別游標變數防呆

CREATE OR REPLACE PACKAGE pkg_hr IS
  TYPE emp_cursor IS REF CURSOR RETURN employees%ROWTYPE;
  FUNCTION get_emps(p_dept_id NUMBER) RETURN emp_cursor;
END pkg_hr;
/

CREATE OR REPLACE PACKAGE BODY pkg_hr IS
  FUNCTION get_emps(p_dept_id NUMBER) RETURN emp_cursor IS
    v_cur emp_cursor;
  BEGIN
    OPEN v_cur FOR SELECT * FROM employees WHERE department_id = p_dept_id;
    RETURN v_cur;
  END;
END pkg_hr;
/

-- 呼叫端：編譯期保證結構匹配
DECLARE
  v_cur pkg_hr.emp_cursor;
  v_rec employees%ROWTYPE;
BEGIN
  v_cur := pkg_hr.get_emps(30);
  FETCH v_cur INTO v_rec;  -- 型別安全
  CLOSE v_cur;
END;
/

進階主題：BULK COLLECT 與游標變數

DECLARE
  v_cur SYS_REFCURSOR;
  TYPE t_tab IS TABLE OF employees%ROWTYPE;
  l_tab t_tab;
BEGIN
  OPEN v_cur FOR SELECT * FROM employees WHERE department_id = 30;
  
  -- 批次提取
  FETCH v_cur BULK COLLECT INTO l_tab LIMIT 100;
  
  FORALL i IN 1..l_tab.COUNT
    INSERT INTO emp_archive VALUES l_tab(i);
  
  CLOSE v_cur;
END;
/

常見錯誤與最佳實務

選擇指南

需要動態 SQL / 參數化查詢 / 跨程序傳遞結果集 / 框架介面
        ↓
使用游標變數 (SYS_REFCURSOR 或強型別 REF CURSOR)

固定查詢邏輯 / 簡單遍歷 / 區塊內部使用
        ↓
使用靜態游標 (CURSOR ... IS)

需要編譯期結構檢查 / API 契約約束
        ↓
使用強型別 REF CURSOR RETURN type

最大彈性 / 快速原型 / 異構查詢重用
        ↓
使用弱型別 SYS_REFCURSOR

總結

• 靜態游標 = 編譯期綁定，簡單、效能略優、不可傳遞
• 游標變數 = 執行期綁定，彈性、可傳遞、支援動態 SQL
• 強型別 = 安全、自文件化、編譯期檢查
• 弱型別 = 彈性、通用、執行期驗證
• 現代開發傾向：Package 介面使用強型別 REF CURSOR，內部實作靈活切換