1).创建包头
create or replace PACKAGE my_package is
type res_str is table of varchar2(100);
function splitStr(p_str in varchar2, p_split in varchar2)
return res_str
pipelined;
end my_package;
2).创建包体
create or replace package body my_package is
function splitStr(p_str in varchar2, p_split in varchar2)
return res_str
pipelined as
v_len number := length(p_str);
v_start number := 1;
v_index number;
begin
while (v_start <= v_len) loop
v_index := instr(p_str, p_split, v_start);
if v_index = 0 then
pipe row(substr(p_str, v_start));
v_start := v_len + 1;
else
pipe row(substr(p_str, v_start, v_index - v_start));
v_start := v_index + 1;
end if;
end loop;
return;
end splitStr;
end my_package;
3).测试
select * from table(my_package.splitStr('lisi,zhangshan,wangwu,xiaoxiao,haihai,tuotuo',','));
运行结果: