Oracle存储过程使用传入VARCHAR2参数构造IN查询条件时返回空结果

查询无结果的本质是静态SQL场景下，IN 语法的解析逻辑和直觉存在偏差。
你拼接得到的v_read_batches虽然内容看起来是'100','1000','11','9200'，但它本质是一个独立的长字符串变量，不是多个独立的匹配值。
当你在存储过程里写WHERE to_char(BATCH_ID) IN (v_read_batches)时，Oracle在静态SQL解析阶段，根本不会把这个变量的内容拆成多个值来做IN匹配，实际执行的逻辑等价于：

-- 实际执行的等值判断，不是你预期的多值匹配
WHERE to_char(BATCH_ID) = '''100'',''1000'',''11'',''9200'''

这就是为什么你硬编码写4个独立的字符串字面量时能查到结果——硬编码的IN列表在解析阶段就会被识别成4个独立的匹配项，和塞单个长字符串变量的执行逻辑完全不同。

按性能和规范性从优到劣排序：

• 方案一：使用自定义集合类型实现多值匹配（推荐）
  全程走静态SQL，能正常命中索引，执行计划复用性最好，没有注入风险。
  操作步骤：
  1. 提前创建一次全局字符串集合类型（一般生产库会预先创建，不存在的话执行一次即可）：

     CREATE OR REPLACE TYPE str_list IS TABLE OF VARCHAR2(100);
     

  2. 改写存储过程逻辑，不再手动拼接带单引号的字符串，直接把逗号分隔的入参拆成集合，用集合做IN匹配：

     CREATE OR REPLACE procedure test_pro(read_batch in varchar2 )
     as
     -- 定义集合变量存拆分后的批次值
     v_batches str_list;
     begin
     -- 直接把逗号分隔的入参拆成集合元素，比如输入'100,1000,11,9200'会拆成4个独立元素
     SELECT REGEXP_SUBSTR(read_batch, '[^,]+', 1, LEVEL)
     BULK COLLECT INTO v_batches
     FROM DUAL
     CONNECT BY LEVEL <= REGEXP_COUNT(read_batch, ',') + 1;
     
     -- 内层UNION查询逻辑完全不需要改动
     SELECT CODE,BANK_NAME_ARABIC,BANK_CODE,to_number(BATCH_ID)BATCH_ID 
     FROM (
         select 1 CODE,PB.BANK_NAME_ARABIC ,to_char(PB.BANK_CODE)BANK_CODE,
         CASE PB.BANK_CODE
             WHEN 1000 THEN 1000 
             WHEN 100 THEN 100  
             ELSE 9200 
         END batch_id
         from BANKS PB 
         WHERE PB.BANK_CODE IN (1000,100,11200)
         union
         SELECT 2 CODE,'Other Banks' other_banks,listagg(PB.BANK_CODE , ', ')
         within group(order by  PB.BANK_CODE ) as BANK_CODE, 11 batch_id
         FROM BANKS  PB 
         WHERE PB.BANK_CODE NOT IN (1000,100,9200)
     )
     -- 直接用集合做成员匹配，替代原来的IN长字符串写法
     WHERE to_char(BATCH_ID) MEMBER OF v_batches;
     end test_pro;
     

• 方案二：使用动态SQL实现匹配
  动态SQL会在执行前把拼接的完整语句交给Oracle解析，这时候IN后面嵌入的拼接字符串才会被识别成多个独立的匹配值。但要注意两个问题：一是要对入参做转义处理，避免SQL注入风险；二是动态SQL的执行计划复用性比静态SQL差。
  核心逻辑是把你原来的完整SQL拼好，把v_read_batches直接嵌到IN括号里，再执行拼好的语句。
• 避坑提醒
  不要图省事用INSTR做模糊匹配，比如写WHERE INSTR(','||v_read_batches||',', ','||BATCH_ID||',') > 0，这种写法无法命中普通B树索引，数据量上来后查询性能会大幅下降。

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