Oracle 多條件動態查詢分頁預存程序：靈活 WHERE 條件 + ROWNUM 分頁實戰

多條件查詢 + 分頁：單一預存程序解決動態 WHERE 與分頁

實務上常遇到「查詢條件不固定、需分頁」的需求。本文展示一個完整預存程序 PROC_CLIENT_LIST：10 個可選查詢條件 (字串 LIKE、日期範圍、數值等值)、ROWNUM 雙層分頁、綁定變數防注入、單次往返回傳游標。

預存程序完整代碼

CREATE OR REPLACE PROCEDURE PROC_CLIENT_LIST (
    P_CURSOR       OUT SYS_REFCURSOR,   -- 結果集游標
    P_CHARACTERS   IN  VARCHAR2,        -- 客戶性質
    P_STATES       IN  VARCHAR2,        -- 客戶狀態
    P_TYPE         IN  VARCHAR2,        -- 客戶類型
    P_CALLING      IN  VARCHAR2,        -- 客戶行業
    P_NAME         IN  VARCHAR2,        -- 客戶名稱
    P_BEGINTIME    IN  DATE,            -- 建立日期起
    P_ENDTIME      IN  DATE,            -- 建立日期迄
    P_AREA         IN  NUMBER,          -- 客戶地區
    P_SOURCE       IN  VARCHAR2,        -- 客戶來源
    P_IMPORTENT    IN  VARCHAR2,        -- 重要程度
    P_START_ROW    IN  NUMBER,          -- 起始列 (1-based)
    P_END_ROW      IN  NUMBER           -- 結束列
) IS
    V_SQL_STR VARCHAR2(3000);
BEGIN
    -- 動態組裝 SQL：三層 ROWNUM 分頁 + 所有條件用 :param IS NULL OR 寫法
    V_SQL_STR := 
    'SELECT * FROM (
        SELECT ROW_.*, ROWNUM ROWNUM_ FROM (
            SELECT * FROM CLIENTINFO C
            WHERE (:CHARACTERS IS NULL OR C.CHARACTERS LIKE :CHARACTERS_LIKE)
              AND (:STATES IS NULL OR C.STATES LIKE :STATES_LIKE)
              AND (:TYPE IS NULL OR C.TYPE LIKE :TYPE_LIKE)
              AND (:CALLING IS NULL OR C.CALLING LIKE :CALLING_LIKE)
              AND (:NAME IS NULL OR C.NAME LIKE :NAME_LIKE)
              AND (:BEGINTIME IS NULL OR C.CREATETIME > :BEGINTIME)
              AND (:ENDTIME IS NULL OR C.CREATETIME < :ENDTIME)
              AND (:AREA IS NULL OR C.AREA = :AREA)
              AND (:SOURCE IS NULL OR C.CLIENTSOURCE LIKE :SOURCE_LIKE)
              AND (:IMPORTENT IS NULL OR C.IMPORTENT LIKE :IMPORTENT_LIKE)
        ) ROW_
        WHERE ROWNUM <= :END_ROW
    ) WHERE ROWNUM_ > :START_ROW';

    -- 開啟游標，USING 子句按佔位符順序綁定 (每個 LIKE 條件需傳兩次：原值 + 加 % 值)
    OPEN P_CURSOR FOR V_SQL_STR USING
        P_CHARACTERS, '%' || P_CHARACTERS || '%',
        P_STATES,     '%' || P_STATES     || '%',
        P_TYPE,       '%' || P_TYPE       || '%',
        P_CALLING,    '%' || P_CALLING    || '%',
        P_NAME,       '%' || P_NAME       || '%',
        P_BEGINTIME,  P_BEGINTIME,
        P_ENDTIME,    P_ENDTIME,
        P_AREA,       P_AREA,
        P_SOURCE,     '%' || P_SOURCE     || '%',
        P_IMPORTENT,  '%' || P_IMPORTENT  || '%',
        P_END_ROW,    P_START_ROW;
END PROC_CLIENT_LIST;
/

設計亮點解析

Java/iBATIS/MyBatis 呼叫範例

// MyBatis Mapper
@Select("{call PROC_CLIENT_LIST(#{cursor, mode=OUT, jdbcType=ORACLE_CURSOR, resultMap=ClientMap}, " +
        "#{characters}, #{states}, #{type}, #{calling}, #{name}, " +
        "#{beginTime}, #{endTime}, #{area}, #{source}, #{importent}, " +
        "#{startRow}, #{endRow})}")
List queryClients(@Param("characters") String characters, ...);

// 或 XML

    {call PROC_CLIENT_LIST(
        #{cursor, mode=OUT, jdbcType=ORACLE_CURSOR, resultMap=ClientMap},
        #{characters}, #{states}, #{type}, #{calling}, #{name},
        #{beginTime}, #{endTime}, #{area}, #{source}, #{importent},
        #{startRow}, #{endRow}
    )}

效能優化建議

1. 建立複合索引：依常用查詢組合建立 IDX_CLIENT_QUERY (CHARACTERS, STATES, CREATETIME) 等
2. 避免 SELECT *：明確列出需要欄位，減少網路傳輸與緩衝區壓力
3. 大表分頁優化：若資料量 > 100萬，考慮 ROWID 分頁或延遲關聯 (先查 PK 再 JOIN 明細)
4. 統計資訊：定期 DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','CLIENTINFO')

Oracle 12c+ 現代化寫法

-- 12c 起支援 OFFSET ... FETCH，程式碼更簡潔
OPEN P_CURSOR FOR 
    SELECT * FROM CLIENTINFO C
    WHERE (:CHARACTERS IS NULL OR C.CHARACTERS LIKE :CHARACTERS_LIKE)
      ...
    ORDER BY C.CREATETIME DESC
    OFFSET :START_ROW ROWS FETCH NEXT :PAGESIZE ROWS ONLY;

但 10g/11g 仍廣泛使用，ROWNUM 三層寫法為相容性最佳方案。