Oracle 動態游標 (REF CURSOR) 開啟、抓取、關閉完整範例與屬性詳解

Oracle 動態游標 (REF CURSOR) 開啟、抓取、關閉完整範例與屬性詳解

動態游標 (REF CURSOR) 允許在執行期決定查詢語句，是構建靈活資料存取層、報表引擎、框架封裝的核心技術。本文提供標準宣告、開啟、抓取、關閉流程範例，並詳細說明游標屬性應用。

1. 完整範例：參數化動態查詢

DECLARE
  v_col1   VARCHAR2(254);
  v_col2   VARCHAR2(254);
  v_sql    VARCHAR2(1024);
  TYPE my_cursor IS REF CURSOR;
  v_cur    my_cursor;
BEGIN
  -- 動態 SQL 含佔位符
  v_sql := 'SELECT :1, :2 FROM dual WHERE 1 = :3';

  -- 開啟游標並綁定參數
  OPEN v_cur FOR v_sql USING 'col1', 'col2', 1;

  -- 迴圈抓取
  LOOP
    FETCH v_cur INTO v_col1, v_col2;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_col1 || ' ' || v_col2);
  END LOOP;

  -- 關閉游標釋放資源
  CLOSE v_cur;
END;
/

2. 顯式游標屬性完整對照表

3. 進階應用場景

場景 A：函數回傳 REF CURSOR 給客戶端 (Java/.NET/Node.js)

CREATE OR REPLACE FUNCTION get_emp_cursor(p_deptno NUMBER)
RETURN SYS_REFCURSOR IS
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR
    SELECT empno, ename, sal
    FROM   emp
    WHERE  deptno = p_deptno;
  RETURN v_cur;
END;
/

場景 B：弱型別 vs 強型別 REF CURSOR

• 弱型別 (SYS_REFCURSOR)：無固定回傳結構，通用性最高，適用於動態查詢。
• 強型別：TYPE t_emp_cur IS REF CURSOR RETURN emp%ROWTYPE; 編譯期檢查結構，安全性較高。

場景 C：批量抓取優化

FETCH v_cur BULK COLLECT INTO v_arr LIMIT 1000;

搭配 LIMIT 避免一次性抓取過多資料導致 PGA 暴增。

4. 常見錯誤與最佳實踐

1. 忘記 CLOSE：導致游標洩漏，最終觸發 ORA-01000: maximum open cursors exceeded。務必在 EXCEPTION 區塊也 CLOSE。
2. 參數綁定順序錯誤：USING 參數須與 SQL 中佔位符 :1, :2... 位置一一對應。
3. 游標變數作為 OUT 參數傳遞：呼叫端需宣告同型別變數接收，並在用畢後關閉。

掌握 REF CURSOR 完整生命週期管理，能構建高復用、高效能的資料存取模組。