Oracle 存儲過程利用 REF CURSOR 返回結果集：Package、Function、Procedure 與 Java JDBC 完整實現

三層架構實現：Package Spec → Function/Procedure → Java Client

Layer 1：Package 定義 REF CURSOR 型別（共享契約）

CREATE OR REPLACE PACKAGE pkg_cursor_types AS
    -- 弱型別 REF CURSOR：可指向任意查詢結構
    TYPE weak_ref_cursor IS REF CURSOR;
    
    -- 強型別 REF CURSOR：綁定特定 RECORD 型別 (可選，增強型別安全)
    TYPE emp_rec IS RECORD (
        empno    NUMBER(4),
        ename    VARCHAR2(10),
        job      VARCHAR2(9),
        sal      NUMBER(7,2)
    );
    TYPE strong_ref_cursor IS REF CURSOR RETURN emp_rec;
END pkg_cursor_types;
/
SHOW ERRORS

Layer 2.1：Function 返回 REF CURSOR（查詢邏輯封裝）

CREATE OR REPLACE FUNCTION fn_get_emp_cursor
RETURN pkg_cursor_types.weak_ref_cursor
AS
    l_cursor pkg_cursor_types.weak_ref_cursor;
BEGIN
    OPEN l_cursor FOR
        SELECT empno, ename, job, sal
        FROM emp
        WHERE deptno = 20
        ORDER BY empno;
    RETURN l_cursor;
END fn_get_emp_cursor;
/
SHOW ERRORS

Layer 2.2：Procedure 以 OUT 參數返回 REF CURSOR（多結果集/複雜邏輯）

CREATE OR REPLACE PROCEDURE prc_get_emp_cursors(
    p_deptno    IN  NUMBER,
    p_cursor1   OUT pkg_cursor_types.weak_ref_cursor,  -- 員工明細
    p_cursor2   OUT pkg_cursor_types.weak_ref_cursor   -- 部門彙總
) AS
BEGIN
    -- 游標 1：員工明細
    OPEN p_cursor1 FOR
        SELECT empno, ename, job, sal, hiredate
        FROM emp
        WHERE deptno = p_deptno
        ORDER BY empno;

    -- 游標 2：部門彙總
    OPEN p_cursor2 FOR
        SELECT d.deptno, d.dname, COUNT(e.empno) emp_cnt, AVG(e.sal) avg_sal
        FROM dept d
        LEFT JOIN emp e ON e.deptno = d.deptno
        WHERE d.deptno = p_deptno
        GROUP BY d.deptno, d.dname;
END prc_get_emp_cursors;
/
SHOW ERRORS

Layer 3：Java JDBC 調用完整範例

import java.sql.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleDriver;

public class RefCursorDemo {
    public static void main(String[] args) throws Exception {
        // 1. 註冊驅動 (JDBC 4.0+ 可省略 Class.forName)
        // Class.forName("oracle.jdbc.driver.OracleDriver");
        
        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
        String user = "scott";
        String pwd = "tiger";
        
        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
            conn.setAutoCommit(false);
            
            // --- 調用 Function 返回 REF CURSOR ---
            String funcCall = "{ ? = call fn_get_emp_cursor }";
            try (CallableStatement cstmt = conn.prepareCall(funcCall)) {
                cstmt.registerOutParameter(1, OracleTypes.CURSOR);
                cstmt.execute();
                
                try (ResultSet rs = (ResultSet) cstmt.getObject(1)) {
                    System.out.println("=== Function 返回結果 ===");
                    while (rs.next()) {
                        System.out.printf("EMPNO=%d, ENAME=%s, JOB=%s, SAL=%.2f%n",
                            rs.getInt("EMPNO"), rs.getString("ENAME"),
                            rs.getString("JOB"), rs.getDouble("SAL"));
                    }
                }
            }
            
            // --- 調用 Procedure 返回雙 REF CURSOR ---
            String procCall = "{ call prc_get_emp_cursors(?, ?, ?) }";
            try (CallableStatement cstmt = conn.prepareCall(procCall)) {
                cstmt.setInt(1, 20);                    // IN p_deptno
                cstmt.registerOutParameter(2, OracleTypes.CURSOR); // OUT p_cursor1
                cstmt.registerOutParameter(3, OracleTypes.CURSOR); // OUT p_cursor2
                cstmt.execute();
                
                // 結果集 1
                try (ResultSet rs1 = (ResultSet) cstmt.getObject(2)) {
                    System.out.println("\n=== Procedure 游標1: 員工明細 ===");
                    while (rs1.next()) {
                        System.out.printf("EMPNO=%d, ENAME=%s, JOB=%s, SAL=%.2f, HIREDATE=%s%n",
                            rs1.getInt("EMPNO"), rs1.getString("ENAME"),
                            rs1.getString("JOB"), rs1.getDouble("SAL"),
                            rs1.getDate("HIREDATE"));
                    }
                }
                
                // 結果集 2
                try (ResultSet rs2 = (ResultSet) cstmt.getObject(3)) {
                    System.out.println("\n=== Procedure 游標2: 部門彙總 ===");
                    while (rs2.next()) {
                        System.out.printf("DEPTNO=%d, DNAME=%s, EMP_CNT=%d, AVG_SAL=%.2f%n",
                            rs2.getInt("DEPTNO"), rs2.getString("DNAME"),
                            rs2.getInt("EMP_CNT"), rs2.getDouble("AVG_SAL"));
                    }
                }
            }
            
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

進階技巧與最佳實踐

1. 動態 SQL 建立 REF CURSOR（執行時決定查詢）

CREATE OR REPLACE PROCEDURE prc_dynamic_query(
    p_table_name IN VARCHAR2,
    p_where_clause IN VARCHAR2,
    p_cursor OUT pkg_cursor_types.weak_ref_cursor
) AS
    v_sql VARCHAR2(4000);
BEGIN
    -- 防 SQL 注入：驗證表名、欄位名白名單
    IF p_table_name NOT IN ('EMP','DEPT','SALGRADE') THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid table name');
    END IF;
    
    v_sql := 'SELECT * FROM ' || p_table_name || ' WHERE ' || p_where_clause;
    OPEN p_cursor FOR v_sql;
EXCEPTION
    WHEN OTHERS THEN
        -- 記錄錯誤日誌
        RAISE;
END;

2. REF CURSOR 轉 JSON/XML（現代 API 直輸）

-- 12c+ JSON 輸出
CREATE OR REPLACE FUNCTION fn_emp_json RETURN CLOB AS
    l_cursor SYS_REFCURSOR;
    l_json   CLOB;
BEGIN
    OPEN l_cursor FOR SELECT * FROM emp WHERE deptno = 20;
    -- 使用 DBMS_SQL 搭配 JSON 產生 (簡化示意)
    -- 實際建議用 ORDS / APEX_JSON / 自建 PL/JSON 庫
    RETURN l_json;
END;

3. 關閉游標與資源管理鐵律

記憶體洩漏風險：未關閉的 REF CURSOR 佔用 PGA 記憶體，大量併發會導致 ORA-04030。Java 端**務必使用 try-with-resources** 確保 ResultSet.close() 執行。

4. SYS_REFCURSOR：內建弱型別，無需自建 Package

-- 簡化版：直接用 Oracle 內建型別
CREATE OR REPLACE FUNCTION fn_simple_cursor RETURN SYS_REFCURSOR AS
    l_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_cursor FOR SELECT * FROM dept;
    RETURN l_cursor;
END;

常見錯誤與排查

總結

**Package 定義型別 → Function/Procedure 開啟並返回 REF CURSOR → JDBC CallableStatement 註冊 OracleTypes.CURSOR 獲取 ResultSet** 是 Oracle 存儲過程返回結果集的標準模式。掌握弱/強型別選擇、動態 SQL 安全構建、Java 端資源管理鐵律，能構建高效、安全、可維護的資料存取層。現代架構中，建議搭配 ORDS (Oracle REST Data Services) 將 REF CURSOR 直接轉為 RESTful JSON API，減少中間層樣板代碼。