Oracle存储过程动态构建WHERE子句IN条件多值查询失效问题求助

问题现状 #

• 存储过程内尝试动态构建IN查询条件，单值传入时查询可正常运行，多值传入时IN过滤逻辑不生效
• 已通过DBMS_OUTPUT校验生成的条件内容，格式完全符合预期，所有实现逻辑都封装在存储过程内部

现有实现代码 #

多值拼接实现 #

xq:=q'#select '('||q'[']'||REPLACE(UPPER(:pc),',',q'[',']')||q'[']'||')'  from dual#';
EXECUTE IMMEDIATE xq BULK COLLECT
        INTO qu 
       using pclient_code;
pclient_code2 := qu(1);

后续查询WHERE子句写法 #

where
acv.client_code in case when pclient_code='*' then acv.client_code else pclient_code2 end

存储过程接口定义 #

(pclient_code IN VARCHAR2,pclient_loc IN VARCHAR2,plob IN VARCHAR2,pclient_sube IN VARCHAR2, c_data IN OUT APP_NADS.PKG_STAND_SUBRO_ITD_ALL.type_stand_subro_itd_ALL)

问题核心根因 #

当前实现存在语法逻辑层面的本质缺陷：
静态SQL场景下，IN关键字后无法识别运行时生成的字符串形式的多值列表。
你现在用CASE表达式返回的pclient_code2，哪怕内容是('A','B','C')的标准IN列表格式，数据库在执行静态SQL时，也只会把这串完整字符串当成单个标量值，直接和acv.client_code字段做等值比对：

• 单值场景下，拼接生成的字符串和实际字段值长度、内容匹配时，查询会返回正确结果，看起来功能正常
• 多值场景下，拼接生成的是带多个引号、逗号的长字符串，和实际存储的短字段值完全无法匹配，就会出现过滤条件失效、查不出数据的问题

可落地的解决思路 #

• 优先选用静态SQL拆分多值的方案，完全规避动态字符串解析问题
  不需要提前拼接带括号、引号的IN值串，直接在查询内把逗号分隔的入参值拆成多行集合，作为IN子查询的匹配数据源。比如通过Oracle内置的XMLTable函数拆分逗号串，示例写法：

  -- 改造后的WHERE逻辑
  WHERE
    (pclient_code = '*' 
     OR acv.client_code IN (
        SELECT UPPER(TRIM(COL_VALUE))
        FROM XMLTABLE(('"' || REPLACE(pclient_code, ',', '","') || '"'))
     )
    )
  

  这种写法不需要额外写动态执行逻辑，单值、多值入参都能被数据库识别为正常的IN匹配集合，不会出现把整串内容当单值匹配的问题。
• 若必须使用动态拼接的IN值串，需将整个查询改造为全量动态SQL
  不要在静态SQL里嵌CASE返回IN字符串的写法，把整个查询的SQL文本动态拼接出来，将你之前生成好的('A','B','C')格式的值列表，直接拼接到IN关键字后面的括号位置，再用EXECUTE IMMEDIATE执行完整的动态SQL。只有在动态SQL的编译过程中，这部分拼接的字符串才会被数据库解析为真正的IN多值列表，而不是单个普通字符串。
• 避坑注意：静态SQL的语法结构在编译阶段就已固定，运行时传入的字符串参数，只会作为数据值参与比对，不会被重新解析为SQL语法的一部分，这也是你生成的字符串格式完全符合预期、但功能失效的核心原因。

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