Oracle函数中如何将逗号分隔列表作为IN子句参数传入

你的函数返回null主要有两个核心问题，咱们一步步拆解清楚：

1. 未给返回变量赋值 #

你声明了count_emp作为返回变量，但在SELECT语句里没有把查询结果赋值给它——这直接导致不管查询逻辑有没有结果，count_emp始终是初始的null值。

2. IN子句的使用误区 #

你传入的参数是'''AGRICULTURE'',''IT'''，实际传递的字符串是'AGRICULTURE','IT'，但Oracle会把整个字符串当成单一值去和deptname匹配，而不是自动拆分成两个独立的部门名称。显然你的employees表中没有deptname等于'AGRICULTURE','IT'的记录，所以count(*)结果为0，再加上没赋值的问题，最终返回null。

修复方案一：使用动态SQL（适配你原有的调用方式） #

动态SQL可以把你的参数拼接成合法的IN子句条件，让Oracle正确识别多个部门值：

CREATE OR REPLACE EDITIONABLE FUNCTION "GET_EMPLOYEES_COUNT"(DEPT_STRING VARCHAR2) RETURN VARCHAR2 AS 
    count_emp VARCHAR2(10);
    sql_stmt VARCHAR2(200);
BEGIN
    -- 拼接动态SQL，将传入的部门字符串直接嵌入IN子句
    sql_stmt := 'SELECT count(*) FROM employees WHERE deptname IN (' || DEPT_STRING || ')';
    -- 执行动态SQL并将结果赋值给返回变量
    EXECUTE IMMEDIATE sql_stmt INTO count_emp;
    RETURN count_emp;
END GET_EMPLOYEES_COUNT;

调用时保持你原来的写法即可：

SELECT GET_EMPLOYEES_COUNT('''AGRICULTURE'',''IT''') FROM DUAL;

修复方案二：拆分字符串（更安全，规避SQL注入风险） #

如果担心动态SQL的注入隐患，可以先把不带引号的逗号分隔字符串拆分成多行，再进行匹配：

CREATE OR REPLACE EDITIONABLE FUNCTION "GET_EMPLOYEES_COUNT"(DEPT_STRING VARCHAR2) RETURN VARCHAR2 AS 
    count_emp VARCHAR2(10);
BEGIN
    SELECT count(*)
    INTO count_emp
    FROM employees
    WHERE deptname IN (
        -- 用正则拆分逗号分隔的字符串，得到独立的部门名称
        SELECT TRIM(REGEXP_SUBSTR(DEPT_STRING, '[^,]+', 1, LEVEL))
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(DEPT_STRING, '[^,]+', 1, LEVEL) IS NOT NULL
    );
    RETURN count_emp;
END GET_EMPLOYEES_COUNT;

这时候调用就不需要给部门加引号了，直接传纯部门名称的逗号分隔串：

SELECT GET_EMPLOYEES_COUNT('AGRICULTURE,IT') FROM DUAL;

额外优化提示（Oracle 12c+适用） #

如果你的Oracle版本是12c及以上，可以用JSON_TABLE来更简洁地拆分字符串：

SELECT count(*)
INTO count_emp
FROM employees
WHERE deptname IN (
    SELECT value
    FROM JSON_TABLE('["' || REPLACE(DEPT_STRING, ',', '","') || '"]', '$[*]' COLUMNS value VARCHAR2(50) PATH '$')
);

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