Oracle INSTR + DECODE 實現動態多條件組合查詢：靈活替代動態 SQL

場景：前端多條件可選查詢

用戶可任意組合「流程類型、節點名稱、日期範圍」查詢，傳統做法拼接動態 SQL，難維護且有注入風險。利用 INSTR + DECODE 將條件判斷下推到 SQL 內部，實現單一靜態 SQL覆蓋所有組合。

核心函數語法回顧

INSTR(string1, string2 [, start_position [, nth_appearance]])
-- 返回 string2 在 string1 中的位置，找不到返回 0

DECODE(expr, search1, result1 [, search2, result2 ...] [, default])
-- 等值比較，匹配返回對應結果，否則默認值

實戰 SQL 重構

SELECT e.arrival_date,
       e.role_name,
       d.doc_title,
       d.doc_no,
       e.node_no,
       e.action,
       e.action_date,
       b.instance_no
FROM   wf_type a,
       wf_instance b,
       wf_def c,
       doc d,
       wf_state e
WHERE  a.type_no = c.type_no
AND    e.processed_flag = 'Y'
AND    e.user_id = 'tetdmis'
AND    b.def_no = c.def_no
AND    b.active_no = d.doc_no
AND    c.def_no = b.def_no
AND    e.instance_no = b.instance_no

-- 動態條件 1：流程類型 (傳 -1 表示不限制)
AND INSTR(DECODE(:type_cond, '-1', a.type_no, :type_cond), a.type_no) > 0

-- 動態條件 2：處理日期範圍
AND TO_CHAR(e.action_date, 'YYYY-MM-DD') BETWEEN :start_dt AND :end_dt

-- 動態條件 3：節點動作 (傳 -1 表示不限制)
AND INSTR(DECODE(:action_cond, '-1', e.action, :action_cond), e.action) > 0;

原理解析

1. DECODE(:param, '-1', column, :param)：
   • 參數為 -1 → 返回列值自身 → INSTR(column, column) 恒為 1 (>0) → 條件恒真 (不限制)。
   • 參數為具體值 → 返回該值 → INSTR('value', column) > 0 等價於 column = 'value'。
2. 多值匹配擴展：若前端傳 'A,B,C'，可用 INSTR(','||:param||',', ','||column||',') > 0 實現 IN 列表效果。

性能與優化建議

• 優點：單一執行計劃，游標共享，無硬解析風暴。
• 缺點：INSTR/DECODE 導致列上索引失效 (函數調用)，全表掃描風險。
• 改進：
  • 建立函數索引：CREATE INDEX idx_wf_state_action_fn ON wf_state(INSTR(DECODE(:b1,'-1',action,:b1), action)) (11g+ 支持)。
  • 核心過濾條件 (如日期、用戶 ID) 保持原生列比較，走索引；僅可選條件用此技巧。
  • 數據量極大時，改用 SYS_CONTEXT + VPD 或動態 SQL + 綁定變量。

現代替代方案 (12c+)

-- 使用 CASE 表達式語義更清晰
AND CASE
      WHEN :type_cond = '-1' THEN 1
      WHEN a.type_no = :type_cond THEN 1
      ELSE 0
    END = 1

優化器對 CASE 短路求值支持更好，可配合 OPT_PARAM('_optimizer_filter_pushdown','false') 控制下推。