分割字符串的函数
create or replace function sf_Split_String(
sourceString varchar2,--待分割字符串，格式如'wangqingku,shanghai3h'
subString varchar2--分隔符
)
return Varchar2Varray
is
len integer:=length(subString);
lastpos integer:=1-len;
pos integer;
num integer;
i integer:=1;
ret Varchar2Varray:=Varchar2Varray(null);
begin
/*create or replace type Varchar2Varray is varray(100) of varchar2(40);*/
/*
Create By: wangqingkun@shanghai3h.com
Create Date:2010-07-02
Description:分割字符串返回字符串列表
select * from table(cast(sf_Split_String('Tube,YueXia,ZhangYongLi',',') as Varchar2Varray))
*/
loop
pos:=instr(sourceString,subString,lastpos+len);
if pos>0 then --found
num:=pos-(lastpos+len);
else--no found
num:=length(sourceString)+1-(lastpos+len);
end if;
if i>ret.last then
ret.extend;
end if;
ret(i):=substr(sourceString,lastpos+len,num);
exit when pos=0;
lastpos:=pos;
i:=i+1;
end loop;
return ret;
end sf_Split_String;