Java 呼叫 Oracle 分頁存儲過程：CallableStatement 與 REF CURSOR 完整實現

Java 呼叫 Oracle 分頁存儲過程：CallableStatement 與 REF CURSOR 完整實現

Oracle 分頁常用 ROWNUM 雙層嵌套，配合 REF CURSOR 返回結果集。本文提供 Java 端 CallableStatement 呼叫完整代碼、PL/SQL 存儲過程與包定義，可直接落地。

Java 端呼叫代碼

public class Test {
    public static void main(String[] args) {
        Connection conn = BaseJdbcDAO.getConnection2("com"); // 自定義連線池
        String sqlSel = "SELECT * FROM POSITION";
        String sqlCount = "SELECT COUNT(*) FROM POSITION";
        String callSql = "{ call SP_PAGE(?,?,?,?,?,?,?) }";

        try (CallableStatement call = conn.prepareCall(callSql)) {
            // 入參
            call.setInt(1, 20);        // pageSize
            call.setInt(2, 100);       // pageNo
            call.setString(3, sqlSel); // 查詢 SQL
            call.setString(4, sqlCount); // 計數 SQL

            // 出參註冊
            call.registerOutParameter(5, Types.INTEGER);      // pageCount
            call.registerOutParameter(6, Types.INTEGER);      // totalCount
            call.registerOutParameter(7, OracleTypes.CURSOR); // REF CURSOR

            call.execute();

            // 獲取結果
            int pageCount = call.getInt(5);
            int total = call.getInt(6);
            try (ResultSet rs = (ResultSet) call.getObject(7)) {
                int i = 1;
                while (rs.next()) {
                    System.out.println(i++ + " 號碼:" + rs.getString(2) + " 名稱:" + rs.getString(3));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Oracle 端：包與存儲過程

1. 定義 REF CURSOR 類型包

CREATE OR REPLACE PACKAGE PKG_QUERY AS
    TYPE refCursorType IS REF CURSOR; -- 遊標類型
END PKG_QUERY;

2. 分頁存儲過程 SP_PAGE

CREATE OR REPLACE PROCEDURE SP_PAGE(
    p_PageSize     IN  NUMBER,           -- 每頁筆數
    p_PageNo       IN  NUMBER,           -- 頁碼（從 1 開始）
    p_SqlSelect    IN  VARCHAR2,         -- 查詢 SQL（含 ORDER BY）
    p_SqlCount     IN  VARCHAR2,         -- 計數 SQL
    p_pageCount    OUT NUMBER,           -- 總頁數
    p_OutRecordCount OUT NUMBER,         -- 總記錄數
    p_OutCursor    OUT PKG_QUERY.refCursorType -- 結果集遊標
) AS
    v_sql       VARCHAR2(3000);
    v_count     NUMBER;
    v_heiRownum NUMBER;
    v_lowRownum NUMBER;
BEGIN
    -- 1. 取總記錄數
    EXECUTE IMMEDIATE p_SqlCount INTO v_count;
    p_OutRecordCount := v_count;

    -- 2. 計算總頁數
    IF MOD(v_count, p_PageSize) = 0 THEN
        p_pageCount := v_count / p_PageSize;
    ELSE
        p_pageCount := v_count / p_PageSize + 1;
    END IF;

    -- 3. 構建分頁查詢（ROWNUM 雙層嵌套）
    v_heiRownum := p_PageNo * p_PageSize;
    v_lowRownum := v_heiRownum - p_PageSize + 1;

    v_sql := 'SELECT B.* FROM (SELECT A.*, ROWNUM rn FROM (' || p_SqlSelect || ') A WHERE ROWNUM <= ' || v_heiRownum || ') B WHERE rn >= ' || v_lowRownum;

    -- 4. 打開遊標返回
    OPEN p_OutCursor FOR v_sql;
END SP_PAGE;

關鍵技術點解析

優化建議

• 生產環境避免直接拼接 SQL，防注入：改用 DBMS_SQL 綁定變數或固定查詢模板。
• 大數據量分頁考慮 ROW_NUMBER() OVER 分析函數（Oracle 12c+ 支援 OFFSET ... FETCH）。
• 連線池、預編譯、批量處理等 JDBC 最佳實踐同樣適用。

推薦參考：Oracle 分頁存儲過程詳解