Oracle 自定义分隔函数

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

Oracle 自定义分隔函数：

create or replace function split_string(t_string in varchar2, p_delimiter in varchar2)return sys.Odcivarchar2listpipelinedisl_idx Pls_integer;v_string varchar(245) := t_string;
beginloopl_idx := instr(v_string, p_delimiter);if l_idx > 0 thenpipe row(substr(v_string, 1, l_idx-1));v_string := substr(v_string, l_idx + length(p_delimiter));elsepipe 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 isselect 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
beginconver_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_SPLITpipelinedisl_idx Pls_integer;v_string varchar(245) := t_string;
beginloopl_idx := instr(v_string, p_delimiter);if l_idx > 0 thenpipe row(substr(v_string, 1, l_idx-1));v_string := substr(v_string, l_idx + length(p_delimiter));elsepipe 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 查询中调用它所以这边不能直接赋值。