ORACLE管道化表函数应用

epemrugtfl

　　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',','));
　　运行结果：