Oracle分頁查詢存儲過程：動態多條件+雙層ROWNUM標準實作

動態WHERE條件結合雙層ROWNUM分頁，單一存儲過程支援任意組合查詢

實戰級分頁查詢存儲過程，採用動態SQL + 條件可選綁定 + 雙層ROWNUM架構，支援10個業務欄位任意組合查詢與高效分頁。

介面設計

CREATE OR REPLACE PROCEDURE proc_client_list(
  pro_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_begin_time   IN  DATE,           -- 建立時間起
  p_end_time     IN  DATE,           -- 建立時間迄
  p_area         IN  NUMBER,         -- 客戶地區
  p_source       IN  VARCHAR2,       -- 客戶來源
  p_importance   IN  VARCHAR2,       -- 重要程度
  p_start_row    IN  NUMBER,         -- 起始行(1-based)
  p_end_row      IN  NUMBER          -- 結束行
)

核心SQL架構：三層巢狀查詢

SELECT * FROM (
  SELECT row_.*, ROWNUM rn FROM (
    SELECT * FROM clientinfo c
    WHERE (:p_characters IS NULL OR c.characters LIKE :p_characters)
      AND (:p_states IS NULL OR c.states LIKE :p_states)
      AND (:p_type IS NULL OR c.type LIKE :p_type)
      AND (:p_calling IS NULL OR c.calling LIKE :p_calling)
      AND (:p_name IS NULL OR c.name LIKE :p_name)
      AND (:p_begin_time IS NULL OR c.createtime > :p_begin_time)
      AND (:p_end_time IS NULL OR c.createtime < :p_end_time)
      AND (:p_area IS NULL OR c.area = :p_area)
      AND (:p_source IS NULL OR c.clientsource LIKE :p_source)
      AND (:p_importance IS NULL OR c.importent LIKE :p_importance)
  ) row_ WHERE ROWNUM <= :p_end_row
) WHERE rn > :p_start_row;

關鍵技術點解析

完整綁定順序(需與USING子句嚴格對應)

OPEN pro_cursor FOR sql_str USING
  p_characters, '%'||p_characters||'%',
  p_states,     '%'||p_states||'%',
  p_type,       '%'||p_type||'%',
  p_calling,    '%'||p_calling||'%',
  p_name,       '%'||p_name||'%',
  p_begin_time, p_begin_time,
  p_end_time,   p_end_time,
  p_area,       p_area,
  p_source,     '%'||p_source||'%',
  p_importance, '%'||p_importance||'%',
  p_end_row,    p_start_row;

12c+ 現代化替代寫法

-- OFFSET/FETCH 語法 (12c+)
SELECT * FROM clientinfo c
WHERE (:p_characters IS NULL OR c.characters LIKE :p_characters)
  ...
OFFSET :p_start_row ROWS FETCH NEXT :page_size ROWS ONLY;
-- 優點：語法簡潔、執行計畫更優、支援語意分頁

效能優化建議

• 高頻查詢欄位建立複合索引，順序依選擇性與WHERE出現頻率
• 大數據量考慮結果集快取(RESULT_CACHE提示)或物化視圖預聚合
• 監控V$SQL_PLAN確認COUNT STOPKEY操作出現(證明ROWNUM停止掃描生效)