Oracle存储过程WHERE子句中如何使用LISTAGG拼接的ID列表参数

IN运算符无法直接将逗号拼接的字符串识别为多个离散值，数据库会尝试将整个'1,2,3,4,5'字符串隐式转换为数字类型，转换失败就会抛出Invalid Number错误。

以下是Oracle场景下的常用实现方案：

方案1：使用JSON_TABLE拆分字符串（Oracle 12c+ 推荐） #

将逗号分隔的字符串转换为JSON数组，再通过JSON_TABLE拆分为多行数字结果集，直接供IN子查询使用：

PROCEDURE abc(id_list IN VARCHAR2) IS
BEGIN
  UPDATE table_a SET flag = 1 
  WHERE id IN (
    SELECT value 
    FROM JSON_TABLE(
      -- 将逗号分隔串转为标准JSON数组格式
      '["' || REPLACE(id_list, ',', '","') || '"]',
      '$[*]' COLUMNS value NUMBER PATH '$'
    )
  );
END;

方案2：使用CONNECT BY递归拆分（兼容Oracle 11g及更早版本） #

通过正则匹配+递归遍历，逐个提取字符串中的ID并转为数字：

PROCEDURE abc(id_list IN VARCHAR2) IS
BEGIN
  UPDATE table_a SET flag = 1 
  WHERE id IN (
    SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(id_list, '[^,]+', 1, LEVEL)))
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(id_list, '[^,]+', 1, LEVEL) IS NOT NULL
  );
END;

方案3：基于集合类型实现 #

如果需要高频复用该逻辑，可以预先定义数字集合类型，将拆分后的ID转为集合后匹配：

1. 预先创建集合类型：

CREATE OR REPLACE TYPE num_table AS TABLE OF NUMBER;
/

2. 存储过程实现：

PROCEDURE abc(id_list IN VARCHAR2) IS
  v_ids num_table;
BEGIN
  SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(id_list, '[^,]+', 1, LEVEL)))
  BULK COLLECT INTO v_ids
  FROM DUAL
  CONNECT BY REGEXP_SUBSTR(id_list, '[^,]+', 1, LEVEL) IS NOT NULL;

  UPDATE table_a SET flag = 1 
  WHERE id MEMBER OF v_ids;
END;

如果拼接的ID数量过多，导致id_list超出VARCHAR2类型的长度上限（默认普通SQL场景为4000字节，PL/SQL场景为32767字节），建议跳过LISTAGG拼接步骤，直接通过游标遍历原始ID数据集处理，避免字符串溢出问题。

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