Oracle 多參數查詢 SQL 技巧：INSTR、REGEXP_SUBSTR 與動態條件構建

Oracle 處理「逗號分隔多值參數」查詢、動態條件構建、字串分列等常見需求，本文提供實用 SQL 模式與 Java 端參數組裝對應方案。

1. 函數解析單一參數值

CREATE OR REPLACE FUNCTION f_get_param(
  in_param IN VARCHAR2,
  in_flag  IN VARCHAR2
) RETURN VARCHAR2 IS
  result   VARCHAR2(500);
  v_len    NUMBER;
  v_left   NUMBER;
  v_right  NUMBER;
  v_param  VARCHAR2(200);
BEGIN
  result := '';
  v_param := in_param;
  v_len   := LENGTH(v_param);
  v_left  := INSTR(v_param, in_flag);
  IF v_left > 0 THEN
    v_param := SUBSTR(v_param, v_left, v_len - v_left + 1);
    v_left  := INSTR(v_param, ':') + 1;
    v_right := INSTR(v_param, '$');
    result  := SUBSTR(v_param, v_left, v_right - v_left);
  END IF;
  RETURN result;
EXCEPTION WHEN OTHERS THEN RETURN '';
END;

用法：f_get_param(v_param, 'materialtype:') 解析 materialtype:value$ 格式。

2. Java 端動態組裝參數字串

public String buildParams(ListArgs args) {
    String params = "$";
    if (args != null && !StringUtils.isEmpty(args.getMaterialType())) {
        params += "materialtype:" + args.getMaterialType() + "$";
    }
    return params;
}

3. 逗號分隔多值查詢：INSTR 技巧

-- 單值存在性檢查
SELECT INSTR(',' || '1,2,3,21' || ',', ',' || '1' || ',') FROM DUAL;  -- 返回 > 0 表示存在

-- 表查詢：list_id 為逗號分隔字串欄位
SELECT * FROM table_name
WHERE INSTR(',' || list_id || ',', ',' || :id || ',') > 0;

4. 字串分列：REGEXP_SUBSTR + CONNECT BY

SELECT DISTINCT REGEXP_SUBSTR('a,b,c', '[^,]+', 1, ROWNUM) AS val
FROM DUAL
CONNECT BY ROWNUM <= LENGTH('a,b,c') - LENGTH(REPLACE('a,b,c', ',')) + 1;

結果：a, b, c 三列。

5. 進階：動態 WHERE 條件構建

-- 想法：將多條件轉為臨時表或集合，再 JOIN
-- 或使用 SYS.ODCIVARCHAR2LIST 集合類型
SELECT * FROM target_table t
WHERE EXISTS (
  SELECT 1 FROM TABLE(CAST(:id_list AS SYS.ODCIVARCHAR2LIST)) v
  WHERE v.COLUMN_VALUE = t.id
);

效能提醒

• INSTR、LIKE 導致全表掃描，適合小表或低頻查詢
• 高頻/大表建議：正規化設計（關聯表）、全文檢索、或應用層拆分多次查詢
• REGEXP_SUBSTR 正則解析有 CPU 開銷，批次處理考慮 PL/SQL 迴圈

選擇正確模式取決於資料量、頻率與維護成本權衡。