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;

-- 函数调用,两个参数:字符串和分隔符

select column_value from table(my_split('7369,7499,7521,7844',','));

-- 输出结果如下

7369

7499

7521

7844

分享到：

2013-01-16 10:29

浏览 8892

分类:数据库

评论