--创建表类型

create or replace type mytype as table of number;
--如果定义成varchar
--CREATE OR REPLACE type mytype as table of varchar2(4000);

-- 将字符串分割成数组

function my_split(piv_str in varchar2, piv_delimiter in varchar2)
--piv_str 为字符串，piv_delimiter 为分隔符
return mytype is
j int := 0;
i int := 1;
len int := 0;
len1 int := 0;
str varchar2(4000);
my_split mytype := mytype();
begin
len := length(piv_str);
len1 := length(piv_delimiter);
while j < len loop
j := instr(piv_str, piv_delimiter, i);
if j = 0 then
j := len;
str := substr(piv_str, i);
my_split.extend;
my_split(my_split.count) := str;
if i >= len then
exit;
end if;
else
str := substr(piv_str, i, j - i);
i := j + len1;
my_split.extend;
my_split(my_split.count) := str;
end if;
end loop;
return my_split;
end my_split;