iBATIS 整合 Oracle 分頁預存程序：動態 SQL、游標回傳與總筆數統計完整範例

iBATIS (MyBatis 前身) 呼叫 Oracle 分頁預存程序實戰

傳統 iBATIS 搭配 Oracle 分頁常用「預存程序 + OUT REF CURSOR」模式。本文提供完整預存程序 P_QUERYSPLIT 實作：支援動態 SQL 片段、自動計算總筆數/總頁數、邊界頁碼修正、FIRST_ROWS Hint 優化，並說明 iBATIS XML 映射配置要點。

Oracle 預存程序：P_QUERYSPLIT

CREATE OR REPLACE PROCEDURE P_QUERYSPLIT (
    P_SQLSCRIPT   IN  VARCHAR2,      -- 基礎查詢 SQL (不含 ORDER BY)
    P_PAGESIZE    IN  INTEGER,       -- 每頁筆數
    P_PAGEINDEX   IN  INTEGER,       -- 當前頁碼 (1-based)
    P_TOTALCOUNT  OUT NUMBER,        -- 總筆數
    P_TOTALPAGE   OUT NUMBER,        -- 總頁數
    P_CUR         OUT SYS_REFCURSOR  -- 回傳游標
) IS
    V_PAGESIZE   NUMBER := NVL(P_PAGESIZE, 1);
    V_PAGEINDEX  NUMBER := NVL(P_PAGEINDEX, 1);
    V_SQL_COUNT  VARCHAR2(4000);
    V_SQL        VARCHAR2(4000);
    V_STARTIDX   NUMBER;
    V_ENDIDX     NUMBER;
BEGIN
    -- 防呆：頁大小至少 1
    IF V_PAGESIZE = 0 THEN V_PAGESIZE := 1; END IF;

    -- 1. 統計總筆數 (包裝原 SQL)
    V_SQL_COUNT := 'SELECT COUNT(*) FROM (' || P_SQLSCRIPT || ')';
    EXECUTE IMMEDIATE V_SQL_COUNT INTO P_TOTALCOUNT;

    -- 2. 計算總頁數
    P_TOTALPAGE := CEIL(P_TOTALCOUNT / V_PAGESIZE);

    -- 3. 修正頁碼 (超過最大頁自動導向最後一頁)
    IF V_PAGEINDEX > P_TOTALPAGE THEN
        V_PAGEINDEX := P_TOTALPAGE;
    END IF;

    -- 4. 計算 ROWNUM 範圍
    V_STARTIDX := (V_PAGEINDEX - 1) * V_PAGESIZE + 1;
    V_ENDIDX   := V_PAGEINDEX * V_PAGESIZE;

    -- 5. 組裝分頁查詢 (Oracle 經典三層巢狀 ROWNUM 寫法 + FIRST_ROWS Hint)
    V_SQL := 'SELECT /*+ FIRST_ROWS */ * FROM (
                  SELECT A.*, ROWNUM RN FROM (
                      ' || P_SQLSCRIPT || '
                  ) A
                  WHERE ROWNUM <= ' || V_ENDIDX || '
              ) WHERE RN >= ' || V_STARTIDX;

    -- 6. 開啟游標回傳
    OPEN P_CUR FOR V_SQL;
END P_QUERYSPLIT;
/

iBATIS SQL Map 配置 (SQLMap.xml)

    {call P_QUERYSPLIT(
        ?,      -- sqlScript (IN)
        ?,      -- pageSize (IN)
        ?,      -- pageIndex (IN)
        ?,      -- totalCount (OUT)
        ?,      -- totalPage (OUT)
        ?       -- cur (OUT CURSOR)
    )}




    
    
    
    
    
    

Java 呼叫範例

Map params = new HashMap<>();
params.put("sqlScript", "SELECT * FROM EMP WHERE DEPTNO = 10 ORDER BY ENAME");
params.put("pageSize", 10);
params.put("pageIndex", 2);

getSqlMapClientTemplate().queryForObject("querySplit", params);

// 取得 OUT 參數
Integer totalCount = (Integer) params.get("totalCount");
Integer totalPage  = (Integer) params.get("totalPage");
List list = (List) params.get("resultCursor"); // 需配合 resultMap 自動映射

關鍵優化細節

遷移至 MyBatis 3.x 的調整

• 改用 @Select 註解或 XML <select> + resultType/resultMap
• OUT CURSOR 對應 java.sql.ResultSet 或自定義 TypeHandler
• 建議改用 MyBatis 內建 RowBounds + 物理分頁插件 (如 PageHelper) 簡化開發

常見問題

1. P_SQLSCRIPT 包含單引號導致動態 SQL 錯誤 → 使用 q'[...]' 引用或雙重單引號轉義
2. 游標未關閉導致洩漏 → 確保iBATIS resultMap 正確配置 javaType="java.sql.ResultSet" 讓框架自動關閉
3. 大數據量 COUNT(*) 緩慢 → 考慮快取總筆數、近似統計或分區表