Oracle 自定义分隔函数

今天上班时，产品经理反馈客户有一批存量数据中，biz_type 存在多个业务键号使用逗号分隔，现在需要将该字段调整为基于逗号分隔的多行记录。

Oracle 自定义分隔函数：

create or replace function split_string(t_string in varchar2, p_delimiter in varchar2)
    return sys.Odcivarchar2list
    pipelined
    is
    l_idx Pls_integer;
    v_string varchar(245) := t_string;
begin
    loop
        l_idx := instr(v_string, p_delimiter);
        if l_idx > 0 then
            pipe row(substr(v_string, 1, l_idx-1));
            v_string := substr(v_string, l_idx + length(p_delimiter));
        else
            pipe row(v_string);
            exit;
        end if;
    end loop;
    return;
end;

调用字符串分隔函数

select * from table (split_string('1, 2, 3'), ',')

输出结果，符合业务要求。将相关SQL 脚本提供给客户现场的运维同事，让他处理剩下的存量数据迁移修正的问题。

中午午休刚刚睡醒，就被产品经理@, 说需要给运维的同事远程支持。

功能要求：存量数据中存在biz_type 字段使用逗号分隔，转换为多行记录。

-- 定义数转存储过程
create or replace procedure conver_procedure is
    -- 定义查询游标
    cursor cur_tid_biz_type is
        select tid, biz_type from **** where biz_type like '%,%';
    -- 定义游标记录
    cur cur_tid_biz_type%ROWTYPE;
    -- 定义集合列表（突然发现,split_string 分隔函数返回的sys.Odcivarchar2list类型是char 类型,无法与兼容，解决办法修改split_string 函数自定义type）
    v_biz_type_list TYPE_SPLIT;
    
    begin
        -- for 循环游标
        for cur in cur_tid_biz_type loop
            -- 调用字符串分隔函数split_string(),将结果存储至自定义集合v_biz_type_list  中。
            select split_string(cur.BIZ_TYPE, ',') into  v_biz_type_list from dual;
            -- 遍历集合
            for i in v_biz_type_list.FIRST .. v_biz_type_list.LAST Loop
                -- 输出结果
                DBMS_OUTPUT.PUT_LINE('tid:'||cur.TID||',bizType:'||v_biz_type_list(i));
            end loop;
        end loop;
    end;   
    

调用存储过程

declare
begin
    conver_procedure()
end;
    

补全自定义Type

create or replace type TYPE_SPLIT as table of varchar2(245);

修改自定义函数split_string的返回数据类型为TYPE_SPLIT.

create or replace function split_string(t_string in varchar2, p_delimiter in varchar2)
    return TYPE_SPLIT
    pipelined
    is
    l_idx Pls_integer;
    v_string varchar(245) := t_string;
begin
    loop
        l_idx := instr(v_string, p_delimiter);
        if l_idx > 0 then
            pipe row(substr(v_string, 1, l_idx-1));
            v_string := substr(v_string, l_idx + length(p_delimiter));
        else
            pipe row(v_string);
            exit;
        end if;
    end loop;
    return;
end;

遇到的问题：

Oracle 应用管道函数时出现PLS-00653：在PL/SQL 定义域内不允许有聚集/表函数。

造成此问题原因：

1、自定义表类型:TYPE_SPLIT

2、自定义字符串分隔函数 :split_string 返回TYPE_SPLIT 管道流。

3、直接调用存储conver_procedure ,去执行split_string 函数，将相关集合赋值给TYPE_SPLIT类型。出现"在PL/SQL 定义域内不允许有聚集/表函数"

错误写法:

v_biz_type_list := split_string(cur.BIZ_TYPE, ',')

正确写法

 select split_string(cur.BIZ_TYPE, ',') into  v_biz_type_list from dual;

因为管道函数需要用TABLE 操作符从SQL 查询中调用它所以这边不能直接赋值。