Oracle 多條件動態查詢分頁存儲過程進階寫法

設計目標

• 支援 10 個以上可選查詢條件（字串模糊、數值精確、日期區間）。
• 條件為 NULL 時自動忽略，不影響查詢邏輯。
• 使用綁定變數，避免硬解析與 SQL 注入。
• 回傳 SYS_REFCURSOR，配合 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_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,    -- 起始列 (含)
  p_end_row      IN  NUMBER     -- 結束列 (含)
) AS
  v_sql VARCHAR2(32767);
BEGIN
  v_sql :=
    'SELECT * FROM ( ' ||
    '  SELECT row_.*, ROWNUM rn FROM ( ' ||
    '    SELECT * FROM client_info c ' ||
    '    WHERE (:characters IS NULL OR c.characters LIKE :characters) ' ||
    '      AND (:states IS NULL OR c.states LIKE :states) ' ||
    '      AND (:type IS NULL OR c.type LIKE :type) ' ||
    '      AND (:calling IS NULL OR c.calling LIKE :calling) ' ||
    '      AND (:name IS NULL OR c.name LIKE :name) ' ||
    '      AND (:begin_time IS NULL OR c.create_time > :begin_time) ' ||
    '      AND (:end_time IS NULL OR c.create_time < :end_time) ' ||
    '      AND (:area IS NULL OR c.area = :area) ' ||
    '      AND (:source IS NULL OR c.client_source LIKE :source) ' ||
    '      AND (:importance IS NULL OR c.importance LIKE :importance) ' ||
    '  ) row_ WHERE ROWNUM <= :end_row ' ||
    ') WHERE rn >= :start_row';

  OPEN p_cursor FOR v_sql
  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;
END proc_client_list;
/
SHOW ERRORS

關鍵技術解析

1. 條件動態過濾模式

:param IS NULL OR column LIKE :param

Oracle 優化器能識別此模式，若參數為 NULL 則不存取該索引/欄位，產出最佳執行計畫。

2. 綁定變數重複使用

每個條件對應 兩個 綁定位置：

• 第 1 個：判斷 IS NULL (原始值)。
• 第 2 個：LIKE 比對 (已加 %)。

日期/數值類型無需 %，直接重複傳遞同一變數。

3. 分頁邏輯：雙層 ROWNUM

• 內層 ROWNUM <= :end_row 限制最大掃描範圍。
• 外層 rn >= :start_row 過濾起始位置。
• 參數順序：USING ... p_end_row, p_start_row 對應 SQL 末端順序。

呼叫範例 (PL/SQL)

DECLARE
  v_cur SYS_REFCURSOR;
  v_rec client_info%ROWTYPE;
BEGIN
  proc_client_list(
    v_cur,
    'VIP', NULL, NULL, NULL, '王%',  -- 只查名稱以「王」開頭的 VIP
    TO_DATE('2023-01-01','YYYY-MM-DD'), TO_DATE('2023-12-31','YYYY-MM-DD'),
    NULL, NULL, NULL,
    1, 20  -- 第 1 頁 20 筆
  );
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN v_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.client_id || ' ' || v_rec.name);
  END LOOP;
  CLOSE v_cur;
END;
/

效能優化建議

• 索引策略：針對高選擇性欄位建立 B-tree 索引；多欄位常用組合可考慮複合索引。
• 統計資訊：定期 DBMS_STATS.GATHER_TABLE_STATS，確保優化器正確評估選擇率。
• 大資料量深分頁：改用 Keyset Pagination (WHERE id > :last_id AND ROWNUM <= :page_size) 避免掃描大量前置列。
• 游標共享：設定 CURSOR_SHARING = FORCE (謹慎) 或確保應用端使用綁定變數，減少硬解析。