请教高手：
我想oracle中将11,22,33,44,...,..,..,不定长度的字符串和中找到逗号(,)，并截去出来放入数组怎么写存储过程？也就是接续“唐人”老师：http://topic.csdn.net/u/20110611/18/fe07773a-2ce5-4931-950b-0c104573eef9.html的内容。
我想oracle中将11,22,33,44,...,..,..,不定长度的字符串和中找到逗号(,)，并截去出来放入数组怎么写存储过程？也就是接续“唐人”老师：http://topic.csdn.net/u/20110611/18/fe07773a-2ce5-4931-950b-0c104573eef9.html的内容。
--提供一个参考
scott@YPCOST> with tb as(
2 select '11;22;3323;abc;ddd' id from dual)
3 select
4 substr(';'||id||';',instr(';'||id||';',';',1,rownum)+1,
5 instr(';'||id||';',';',1,rownum+1)-instr(';'||id||';',';',1,rownum)-1) newid,
6 instr(';'||id||';',';',1,rownum)col1, --';'rownum次出现的位置
7 instr(';'||id||';',';',1,rownum+1)col2, --';'rownum+1次出现的位置
8 instr(';'||id||';',';',1,rownum+1)-instr(';'||id||';',';',1,rownum) col3 --要截取的长度
9 from tb
10 connect by rownum <= length(';'||id||';')
11 - length(replace(';'||id||';', ';', ''))-1
12 /NEWID COL1 COL2 COL3
---------------------------------------- ---------- ---------- ----------
11 1 4 3
22 4 7 3
3323 7 12 5
abc 12 16 4
ddd 16 20 4
function f_split
(
fi_split varchar2,
fi_split_sign varchar2
)
return tp_table
as
str_l_split varchar2(4000);
i_l_index pls_integer;
tp_l_splits tp_table;
begin
str_l_split := fi_split;
loop
i_l_index := instr(str_l_split, fi_split_sign, 1, 1);
if i_l_index > 0 then
tp_l_splits(tp_l_splits.count + 1) := substr(str_l_split, 1, i_l_index - 1);
str_l_split := substr(str_l_split, i_l_index + 1);
else
tp_l_splits(tp_l_splits.count + 1) := str_l_split;
exit;
end if;
end loop;
return tp_l_splits;
end f_split;
SQL>
SQL> DECLARE
2 --声明一数组类型
3 TYPE t_arr IS TABLE OF VARCHAR2(100);
4 --定义一数组变量
5 v_arr t_arr;
6 BEGIN
7 --将分解的字符串存入数组
8 WITH t AS
9 (SELECT '11,22,33,44' str FROM dual)
10 SELECT regexp_substr(str, '[^,]+', 1, LEVEL) BULK COLLECT
11 INTO v_arr
12 FROM t
13 CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1;
14 --输出数组变量值
15 FOR i IN 1 .. v_arr.count LOOP
16 dbms_output.put_line(v_arr(i));
17 END LOOP;
18 END;
19 /
11
22
33
44
PL/SQL procedure successfully completed
SQL>
create or replace package split_pkg
is
type rec_str_split is record(val varchar2(4000));
type ty_str_split is table of rec_str_split;
procedure ora_split (p_str in varchar2,p_delimiter in varchar2) ;
end;
/create or replace package body split_pkg
is
/*
*名称:ora_split
*作者:paddy
*功能:字符串分割功能，如'1;12;123;1234;12345'以分号分隔
*功能类似java的split()
*/
procedure ora_split (
p_str in varchar2, --字符串
p_delimiter in VARCHAR2 --分隔符
)
is
j int := 0;
i int := 1;
len int := 0;
len1 int := 0;
str rec_str_split;
str_split ty_str_split := ty_str_split ();
begin
len := length (p_str);
len1 := length (p_delimiter); while j < len loop
j := instr (p_str, p_delimiter, i);
if j = 0 then
j := len;
str.val := substr (p_str, i);
str_split.extend;
str_split (str_split.count) := str; if i >= len then
exit;
end if;
else
str.val := substr (p_str, i, j - i);
i := j + len1;
str_split.extend;
str_split (str_split.count) := str;
end if;
end loop; --打印数组元素:
for i in 1..str_split.count loop
Dbms_Output.put_line(str_split(i).val);
end loop; end ora_split;
end split_pkg;
/exec split_pkg.ora_split('1;12;123;1234;12345',';'); PL/SQL block, executed in 0 sec.
1
12
123
1234
12345
Total execution time 0.016 sec.
哪种方式效率高些？