如何将拆分字符串为记录的SQL逻辑封装为可复用存储过程/函数

我经常遇到这种需要重复复用字符串拆分逻辑的场景，把这段重复逻辑抽出来封装成表值函数是最优雅的解决方案——既让代码更简洁易维护，又能在任何需要的地方直接调用。

步骤1：创建自定义表类型 #

首先我们需要定义一个承载拆分结果的表类型，用来存储拆分后的序号和对应值：

CREATE OR REPLACE TYPE t_split_result AS OBJECT (
    nbr NUMBER,
    value VARCHAR2(4000)
);
/

CREATE OR REPLACE TYPE t_split_results AS TABLE OF t_split_result;
/

步骤2：编写拆分字符串的表值函数 #

接下来把你原来的拆分逻辑封装成函数，支持自定义分隔符（默认设为逗号，适配日常最常用的场景）：

CREATE OR REPLACE FUNCTION fn_split_string(
    p_input_str VARCHAR2,
    p_delimiter VARCHAR2 DEFAULT ','
) RETURN t_split_results
PIPELINED
IS
    v_element_count NUMBER;
BEGIN
    -- 处理空输入的边界情况
    IF p_input_str IS NULL OR TRIM(p_input_str) = '' THEN
        RETURN;
    END IF;

    -- 计算拆分后的元素总数
    v_element_count := REGEXP_COUNT(p_input_str, p_delimiter) + 1;

    -- 逐行生成拆分结果并返回
    FOR i IN 1..v_element_count LOOP
        PIPE ROW(t_split_result(
            i,
            REGEXP_SUBSTR(p_input_str, '(.*?)(' || p_delimiter || '|$)', 1, i, NULL, 1)
        ));
    END LOOP;

    RETURN;
END;
/

这里用了PIPELINED关键字，能让函数逐行返回结果，处理长字符串时性能更友好。

步骤3：用函数简化你的原SQL #

现在你可以直接调用这个函数，替代原来的子查询，代码瞬间清爽很多：

SELECT test.* 
FROM test 
JOIN TABLE(fn_split_string('1,3')) requested 
    ON test.id = requested.value

如果需要用其他分隔符（比如分号），只需传入第二个参数即可：

SELECT test.* 
FROM test 
JOIN TABLE(fn_split_string('1;3;5', ';')) requested 
    ON test.id = requested.value

额外优化小提示 #

• 如果你的test.id是数值类型，可以把函数里的value字段改成NUMBER类型，或者在函数内部做类型转换，避免隐式转换带来的性能损耗。
• 可以给函数加个参数，自动去除输入字符串首尾的分隔符，防止生成空值行。

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