Oracle 自定义分隔函数

电玩女神 2023-10-10 17:15 22阅读 0赞

今天上班时，产品经理反馈客户有一批存量数据中，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 查询中调用它所以这边不能直接赋值。