Oracle 預存程序回傳結果集：REF CURSOR 完整實作指南

Oracle 預存程序無法像 SQL Server 直接用 SELECT 回傳結果集，必須透過 OUT 參數搭配 SYS_REFCURSOR 或自訂 REF CURSOR 型別實現。本文提供 Package 封裝、動態 SQL、Function 回傳三種完整範本。

方案一：Package + Procedure + 靜態/動態 SQL

-- 1. 定義 Package Spec
CREATE OR REPLACE PACKAGE pkg_test AS
    TYPE t_ref_cursor IS REF CURSOR;
    PROCEDURE get_data(p_id IN NUMBER, p_rc OUT t_ref_cursor);
END pkg_test;
/

-- 2. 實作 Package Body
CREATE OR REPLACE PACKAGE BODY pkg_test AS
    PROCEDURE get_data(p_id IN NUMBER, p_rc OUT t_ref_cursor) IS
        v_sql VARCHAR2(500);
    BEGIN
        IF p_id = 0 THEN
            OPEN p_rc FOR
                SELECT id, name, sex, address, postcode, birthday
                FROM student;
        ELSE
            v_sql := 'SELECT id, name, sex, address, postcode, birthday
                      FROM student WHERE id = :b_id';
            OPEN p_rc FOR v_sql USING p_id;
        END IF;
    END get_data;
END pkg_test;
/

-- 3. 呼叫測試
VAR rc REFCURSOR
EXEC pkg_test.get_data(0, :rc)
PRINT rc

方案二：Function 直接回傳 SYS_REFCURSOR

CREATE OR REPLACE PACKAGE pkg_test AS
    FUNCTION get_data(p_id IN NUMBER) RETURN SYS_REFCURSOR;
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test AS
    FUNCTION get_data(p_id IN NUMBER) RETURN SYS_REFCURSOR IS
        v_rc  SYS_REFCURSOR;
        v_sql VARCHAR2(500);
    BEGIN
        IF p_id = 0 THEN
            OPEN v_rc FOR SELECT id, name, sex, address, postcode, birthday FROM student;
        ELSE
            v_sql := 'SELECT id, name, sex, address, postcode, birthday FROM student WHERE id = :b_id';
            OPEN v_rc FOR v_sql USING p_id;
        END IF;
        RETURN v_rc;
    END get_data;
END pkg_test;
/

-- 呼叫
VAR rc REFCURSOR
EXEC :rc := pkg_test.get_data(1)
PRINT rc

關鍵最佳實務

• 優先使用 SYS_REFCURSOR： 內建弱型別游標，無需在 Package 宣告自訂型別，跨 Schema 呼叫更方便
• 動態 SQL 必須用 USING 綁定變數： 避免 SQL 注入與硬解析，提升共用池利用率
• 游標由呼叫端關閉： Java/.NET/Python 驅動會自動管理，PL/SQL 端若需手動關閉請用 CLOSE cursor_var;
• 大量資料考慮分頁： 在 Procedure 內直接加 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY（12c+）或 ROW_NUMBER() 過濾，避免全量傳輸
• 例外處理： 包裝 OPEN ... FOR 於 BEGIN ... EXCEPTION ... END;，記錄錯誤碼與 SQLCODE 回傳給呼叫端

Java JDBC 呼叫範例

CallableStatement cs = conn.prepareCall("{call pkg_test.get_data(?, ?)}");
cs.setInt(1, 100);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(2);
while (rs.next()) { ... }
rs.close();
cs.close();

掌握 REF CURSOR 模式，是 Oracle PL/SQL 開發與應用整合的核心技能之一。