Oracle 存儲過程返回 REF CURSOR 給 Java 完整範例：Package、Function、Procedure 與 JDBC 調用

Oracle 存儲過程返回 REF CURSOR 給 Java 完整範例：Package、Function、Procedure 與 JDBC 調用

Oracle 透過 REF CURSOR 實現結果集返回給 Java，需建立 Package 定義游標型別、Function/Procedure 打開游標、Java 端用 CallableStatement 註冊輸出參數並取得 ResultSet。本文提供在 SQL*Plus 建立物件與 Java 調用的完整可執行代碼。

1. SQL*Plus 建立 Package、Function、Procedure

-- 1. 定義游標型別 Package
CREATE OR REPLACE PACKAGE types AS
  TYPE cursorType IS REF CURSOR;
END types;
/

-- 2. Function 返回 REF CURSOR
CREATE OR REPLACE FUNCTION sp_ListEmp RETURN types.cursorType AS
  l_cursor types.cursorType;
BEGIN
  OPEN l_cursor FOR SELECT id, title FROM cf_news ORDER BY id;
  RETURN l_cursor;
END;
/

-- 3. Procedure 透過 OUT 參數返回 REF CURSOR
CREATE OR REPLACE PROCEDURE getemps(p_cursor IN OUT types.cursorType) AS
BEGIN
  OPEN p_cursor FOR SELECT id, title FROM cf_news ORDER BY id;
END;
/

2. Java 控制台程式調用 Function

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

public class GetValues {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:database";
        String sql = "BEGIN :1 := sp_ListEmp; END;"; // 調用 Function

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, "scott", "tiger");

        CallableStatement cstmt = conn.prepareCall(sql);
        cstmt.registerOutParameter(1, OracleTypes.CURSOR); // 關鍵：註冊 CURSOR 型別
        cstmt.execute();

        ResultSet rs = (ResultSet) cstmt.getObject(1); // 取得 ResultSet
        while (rs.next()) {
            System.out.println(rs.getString(1)); // 列印 id
        }
        cstmt.close();
        conn.close();
    }
}

3. 調用 Procedure 版本（OUT 參數）

String sql = "{CALL getemps(?)}"; // 或 BEGIN getemps(?); END;
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = (ResultSet) cstmt.getObject(1);
// 後續處理相同

關鍵點總結

常見錯誤與排查

• ORA-06550 / PLS-00103：SQL 語法錯誤，檢查分號、BEGIN/END 包裹。
• Invalid column type：registerOutParameter 未用 OracleTypes.CURSOR 或驅動版本不匹配。
• Closed ResultSet：呼叫 cstmt.close() 會關閉關聯 ResultSet，**先處理完 ResultSet 再關閉 Statement/Connection**。
• 權限不足：執行用戶需有 EXECUTE 權限於 Package/Function/Procedure。

最佳實踐建議

1. 優先使用 **Procedure + OUT 參數**，語義清晰、易於維護。
2. 游標查詢盡量**顯式列出欄位**（SELECT id, title），避免 SELECT * 導致 Java 端欄位索引錯位。
3. 連線池環境下，**確保 CallableStatement 與 Connection 生命週期一致**，避免提前關閉。
4. 大量資料考慮 FETCH SIZE 調優：stmt.setFetchSize(1000)。

掌握 REF CURSOR 雙向互動，能將複雜業務邏輯下沉資料庫層，Java 層專注業務編排，架構更清晰。