oracle不能像mssql那樣支持直接返回表類型，所以要先創建一種自定義類型。這裏用到的是嵌套表（nested table）。
-- nested table
create or replace type split_str is table of varchar(100);
/
-- function
create or replace function fn_split
(
p_str varchar2,
p_delimiter varchar2
)
return split_str pipelined
as
v_str varchar(4000) := p_str;
v_index number;
v_sublength number;
begin
-- p_delimiter is null
if p_delimiter is null then
for x in 1..length(v_str)
loop
pipe row(substr(v_str,x,1));
end loop;
return;
end if;
v_index := instr(v_str,p_delimiter);
while v_index <> 0
loop
pipe row(substr(v_str,1,v_index-1));
v_sublength := length(v_str) - (v_index + length(p_delimiter) - 1);
v_str := substr(v_str,-v_sublength,v_sublength);
v_index := instr(v_str,p_delimiter);
end loop;
pipe row(v_str);
return;
end;
/
-- 調用（第二種方式更好）
select fn_split('this@@is@@it','@@') from dual;
select * from table(fn_split('this@@is@@it','@@'));
更多信息请查看IT技术专栏
