Oracle 回傳結果集的預存程序：REF CURSOR 套件/函數實作完整範例

核心機制

Oracle 預存程序無法直接 SELECT 回傳結果集 (不同於 SQL Server)，必須透過 OUT 參數 + REF CURSOR 將游標指標回傳給呼叫端。

方案一：Package + Procedure (標準企業級模式)

1. Package Specification (介面契約)

CREATE OR REPLACE PACKAGE pkg_student AS
    -- 弱型別 REF CURSOR (支援動態查詢)
    TYPE ref_cursor IS REF CURSOR;
    
    -- 公開程序介面
    PROCEDURE get_students (
        p_deptno IN NUMBER DEFAULT 0,  -- 0 表查全部
        p_result OUT ref_cursor
    );
END pkg_student;
/
-- 弱型別：編譯期不檢查回傳結構，靈活度高
-- 強型別：TYPE strong_ref IS REF CURSOR RETURN student%ROWTYPE; 編譯期檢查，較安全

2. Package Body (實作邏輯)

CREATE OR REPLACE PACKAGE BODY pkg_student AS
    PROCEDURE get_students (
        p_deptno IN NUMBER DEFAULT 0,
        p_result OUT ref_cursor
    ) AS
        v_sql VARCHAR2(500);
    BEGIN
        IF p_deptno = 0 THEN
            -- 靜態查詢：直接 OPEN FOR SELECT
            OPEN p_result FOR
                SELECT id, name, sex, address, postcode, birthday
                FROM student;
        ELSE
            -- 動態查詢：繫結變數 :b_deptno
            v_sql := q'[
                SELECT id, name, sex, address, postcode, birthday
                FROM student
                WHERE deptno = :b_deptno
            ]';
            OPEN p_result FOR v_sql USING p_deptno;
        END IF;
    END get_students;
END pkg_student;
/
-- 關鍵點：
-- 1. OUT 參數 p_result 型別為套件定義的 ref_cursor
-- 2. 動態 SQL 使用 USING 子句綁定輸入參數，防注入
-- 3. 靜態/動態查詢統一透過 OPEN ... FOR 回傳游標

3. 呼叫測試 (SQL*Plus / PL/SQL Developer)

VARIABLE rc REFCURSOR;
EXEC pkg_student.get_students(10, :rc);
PRINT rc;
-- 輸出 deptno=10 的學生結果集

EXEC pkg_student.get_students(0, :rc);
PRINT rc;
-- 輸出所有學生結果集

方案二：Function 回傳 REF CURSOR (較簡潔，適用單一查詢)

CREATE OR REPLACE PACKAGE pkg_student_func AS
    TYPE ref_cursor IS REF CURSOR;
    FUNCTION get_students (p_deptno IN NUMBER DEFAULT 0) RETURN ref_cursor;
END pkg_student_func;
/

CREATE OR REPLACE PACKAGE BODY pkg_student_func AS
    FUNCTION get_students (p_deptno IN NUMBER DEFAULT 0) RETURN ref_cursor AS
        v_rc   ref_cursor;
        v_sql  VARCHAR2(500);
    BEGIN
        IF p_deptno = 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 deptno = :b_deptno';
            OPEN v_rc FOR v_sql USING p_deptno;
        END IF;
        RETURN v_rc;
    END get_students;
END pkg_student_func;
/

-- 呼叫
VARIABLE rc REFCURSOR;
BEGIN
    :rc := pkg_student_func.get_students(20);
END;
/
PRINT rc;

進階：多結果集回傳

CREATE OR REPLACE PACKAGE pkg_multi_result AS
    TYPE ref_cursor IS REF CURSOR;
    PROCEDURE get_dashboard (
        p_deptno IN NUMBER,
        p_emp_rc  OUT ref_cursor,   -- 員工明細
        p_dept_rc OUT ref_cursor,   -- 部門彙總
        p_sal_rc  OUT ref_cursor    -- 薪資統計
    );
END pkg_multi_result;
/

CREATE OR REPLACE PACKAGE BODY pkg_multi_result AS
    PROCEDURE get_dashboard (
        p_deptno IN NUMBER,
        p_emp_rc  OUT ref_cursor,
        p_dept_rc OUT ref_cursor,
        p_sal_rc  OUT ref_cursor
    ) AS
    BEGIN
        OPEN p_emp_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
        OPEN p_dept_rc FOR SELECT * FROM dept WHERE deptno = p_deptno;
        OPEN p_sal_rc FOR 
            SELECT job, AVG(sal) avg_sal, COUNT(*) cnt 
            FROM emp WHERE deptno = p_deptno GROUP BY job;
    END get_dashboard;
END pkg_multi_result;
/
-- 單次呼叫回傳三個游標，減少網路往返

Java/JDBC 呼叫範例

CallableStatement cstmt = conn.prepareCall("{call pkg_student.get_students(?, ?)}");
cstmt.setInt(1, 10);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();

ResultSet rs = (ResultSet) cstmt.getObject(2);
while (rs.next()) {
    System.out.println(rs.getString("name") + " - " + rs.getString("sex"));
}
rs.close();
cstmt.close();

最佳實踐與避坑

1. 游標關閉責任在呼叫端：Java/.NET/Python 必須顯式 ResultSet.close()，否則洩漏游標導致 ORA-01000。
2. 優先使用弱型別 REF CURSOR：支援動態 SQL、多種回傳結構，維護彈性大。僅在嚴格介面契約場景用強型別。
3. 動態 SQL 必須用 USING 綁定變數：OPEN rc FOR v_sql USING p1, p2，嚴禁字串拼接 防 SQL 注入。
4. 避免在循環中 OPEN 游標：批次處理改用 BULK COLLECT 或單一游標回傳聚合結果。
5. Package 封裝：將 REF CURSOR 型別定義於 Package Spec，避免重複定義、便於跨模組共用。
6. 12c+ 隱式結果集：DBMS_SQL.RETURN_RESULT 可讓 Procedure 直接回傳結果集無需 OUT 參數，但相容性較弱，建議仍以 REF CURSOR 為主。