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技术专栏