DB2 v11中WHERE IN使用参数/变量查询的可行解决方案咨询

你之前使用的两个方案存在以下问题：

• 方案1是SQL Server专属语法，DB2不支持@前缀的变量声明，同时IN运算符无法直接识别逗号拼接的字符串，会把整个字符串作为单个值做匹配，自然查不到结果。
• 方案2中使用ANY(val)的写法要求col的字段类型和数组元素类型完全一致，且写法兼容性较差，部分DB2小版本不支持该用法。

以下是3种经过验证的可行方案：

方案1：拆分逗号分隔字符串为临时行集（最通用，适合传入拼接字符串参数的场景） #

如果入参是逗号拼接的字符串，可以用XMLTABLE把字符串拆成单行单值的临时表，再和业务表关联匹配，兼容性覆盖所有DB2 v11版本：

-- 存储过程内声明变量示例，动态查询可直接用?传参
DECLARE v_list VARCHAR(100) = '1,2,3,4';

SELECT t.* 
FROM tbl t
INNER JOIN XMLTABLE(
    '$doc/root/val' 
    PASSING XMLPARSE(DOCUMENT '<root><val>' || REPLACE(v_list, ',', '</val><val>') || '</val></root>') AS "doc"
    COLUMNS val VARCHAR(20) PATH '.'
) AS list ON t.col = list.val;

方案2：数组+UNNEST函数（性能最优，适合数组类型参数的场景） #

如果参数本身是数组类型，可以用UNNEST把数组拆成行集后匹配，效率高于字符串拆分：

-- CTE中使用数组示例
WITH test(val) AS (
    SELECT * FROM UNNEST(ARRAY['5','9'])
)
SELECT t.* 
FROM tbl t
INNER JOIN test ON t.col = test.val;

-- 存储过程内数组变量用法示例
DECLARE v_arr CHAR(1) ARRAY = ARRAY['5','9'];
SELECT t.*
FROM tbl t
WHERE t.col IN (SELECT * FROM UNNEST(v_arr));

方案3：动态SQL拼接（仅适合内部可控场景） #

如果参数完全由内部逻辑生成，不存在SQL注入风险，可以直接拼接SQL执行：

DECLARE v_sql VARCHAR(1000);
DECLARE v_list VARCHAR(100) = '1,2,3,4';

SET v_sql = 'SELECT * FROM tbl WHERE col IN (' || v_list || ')';
PREPARE stmt FROM v_sql;
EXECUTE stmt;

注意：该方案禁止用于存在用户输入的场景，避免SQL注入漏洞

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