Oracle SQL绑定变量在AND/OR过滤中失效问题排查

1. 过滤值标准化不一致：原查询中检查job_title时对:filter_bind做了去空格、转小写处理，但检查id时直接使用原始变量。如果输入的过滤值带空格（比如vp : 98），拼接后的字符串会包含空格，导致:98:无法匹配:vp : 98:，最终返回0行。
2. AND逻辑设计偏离需求：当前逻辑要求job_title匹配过滤值中的任意一项并且id也匹配过滤值中的任意一项，这和你需要的“job_title=vp且id=98”逻辑不符。比如输入vp:98时，若存在job_title=98且id=98的行，也会被错误返回，而真正需要的是过滤值分别对应匹配指定列。

根据你的需求，提供三种修正方案：

方案一：按固定顺序匹配指定列 #

假设过滤值用:分隔，第一个值对应job_title，第二个对应id，AND逻辑下同时满足两项匹配：

select count(*) from my_table
where :my_or_and_bind = 'and' 
and (
  :filter_bind is null
  or (
    -- 匹配第一个过滤值到job_title（去空格、转小写）
    regexp_replace(lower(job_title), '\s+') = regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, 1)
    and
    -- 匹配第二个过滤值到id
    to_char(id) = regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, 2)
  )
)

方案二：所有过滤值必须匹配任意列 #

如果需求是AND逻辑下，输入的每个过滤值都要被job_title或id匹配（比如vp:98要求该行要么job_title包含vp且id=98，要么job_title同时包含vp和98等），可以用以下写法：

select count(*) from my_table
where :my_or_and_bind = 'and' 
and (
  :filter_bind is null
  or (
    -- 检查所有过滤值都能匹配job_title或id
    not exists (
      select 1
      from dual
      connect by regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, level) is not null
      where regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, level) not in (
        regexp_replace(lower(job_title), '\s+'),
        to_char(id)
      )
    )
  )
)

方案三：最小修复原查询（仅解决空格问题） #

如果只想修复空格导致的匹配失败，只需对id的匹配逻辑也做标准化处理：

select count(*) from my_table
where :my_or_and_bind = 'and' 
and (
  :filter_bind is null
  or (
    instr(':' || regexp_replace(lower(:filter_bind), '\s+') || ':', ':' || regexp_replace(lower(job_title), '\s+') || ':') > 0
    and
    -- 新增去空格、转小写处理，和job_title逻辑对齐
    instr(':' || regexp_replace(lower(:filter_bind), '\s+') || ':', ':' || to_char(id) || ':') > 0
  )
)

如果用方案一且需要支持单个值的AND匹配（比如输入vp仅返回job_title=vp的行），可以添加空值判断：

select count(*) from my_table
where :my_or_and_bind = 'and' 
and (
  :filter_bind is null
  or (
    regexp_replace(lower(job_title), '\s+') = regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, 1)
    and
    (regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, 2) is null 
     or to_char(id) = regexp_substr(regexp_replace(lower(:filter_bind), '\s+'), '[^:]+', 1, 2))
  )
)

内容的提问来源于stack exchange，提问作者user25151438