字符串分割成集合
很多时候我们需要把一个字符串分割成多个元素,存放在Collection里面处理。比如Web传送一个逗号分隔的字符串,PL/SQL需要对里面的每个元素进行处理。这实际上是行列转换的一个具体应用。我们习惯使用的两个办法分别是 1. 使用PL/SQL循环分割 2. 使用单条SQL进行分割。 下面测试这两种方式的限制和性能
首先分别使用两种方式构造两个函数
-- use conventional plsql
create or replace function f_str2list_pls
(
p_str varchar2,
p_separator varchar2 default ','
) return my_tk_str_tab_type is
l_idx pls_integer := 0;
l_str varchar2(32767) := trim(p_str);
l_elmt varchar2(100) := null;
l_list my_tk_str_tab_type := my_tk_str_tab_type();
begin
while l_str is not null loop
l_idx := instr(l_str, p_separator);
if l_idx = 0 then
l_elmt := l_str;
l_str := null;
else
l_elmt := substr(l_str, 1, l_idx - 1);
l_str := substr(l_str, l_idx + 1);
end if;
l_list.extend;
l_list(l_list.last) := trim(l_elmt);
end loop;
return l_list;
end;
/
-- use single sql
create or replace function f_str2list_sql
(
p_str varchar2,
p_separator varchar2 default ','
) return my_tk_str_tab_type is
l_list my_tk_str_tab_type := my_tk_str_tab_type();
begin
select substr(a.str,
instr(p_separator || a.str, p_separator, 1, rn),
instr(a.str || p_separator, p_separator, 1, rn) -
instr(p_separator || a.str, p_separator, 1, rn)) q
bulk collect into l_list
from (select p_str as str from dual) a,
(select rownum rn from dual connect by rownum <= length(p_str)) b
where instr(p_separator || a.str, p_separator, 1, rn) > 0;
return l_list;
end;
/
-- 使用正则表达式的写法
select regexp_substr(a.str, '([^,]+)', 1, rn) q
from (select 'aa,bb,cc' as str from dual) a,
(select rownum rn
from dual
connect by rownum <=
(1 + length('a,b,c') - length(replace('a,b,c', ',')))) b
;
确认两种方法完成同样的功能
----------------------------------------------------
-- same result
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(1000) := 'a,b,c';
begin
l_list := f_str2list_pls(l_str,',');
for i in 1..l_list.count loop
dbms_output.put_line(l_list(i));
end loop;
dbms_output.put_line('');
l_list := f_str2list_sql(l_str,',');
for i in 1..l_list.count loop
dbms_output.put_line(l_list(i));
end loop;
end;
/
SQL> set serveroutput on
a
b
c
a
b
c
我们知道在PL/SQL和SQL里面,varchar2类型的长度限制是不同的。那么这两种方法是否也存在同样的限制?验证一下
先测试PL/SQL版本
-- 这里使用单个字母作为元素,加上必要的逗号分割符,一个元素占用长度2. PL/SQL中上限32767。当取(32767/2 = 16383)个元素的时候,运行成功
----------------------------------------------------
-- pls versions tring length limit
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 16383;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_str := substr(l_str, 2);
l_list := f_str2list_pls(l_str, ',');
end;
/
PL/SQL procedure successfully completed
-- 增加一个元素,当取(16384)个元素的时候,超过了varchar2的限制,所以无法运行。(构造字符串就报错了,这个时候其实还没有调用f_str2list_pls)
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 16384;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_list := f_str2list_pls(l_str, ',');
end;
/
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 9
-- 修改一下代码,实际调用这个函数。从结果可以看到,f_str2list_pls()内部报超长错误
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 16383;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_list := f_str2list_pls(l_str||',a', ',');
end;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "T2.F_STR2LIST_PLS", line 7
ORA-06512: at line 12
下面测试SQL版本
-- 因为SQL中varchar2上限是4000,所以使用2000个元素。一切正常。
----------------------------------------------------
-- sql versions tring length limit
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 2000;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_str := substr(l_str, 2);
l_list := f_str2list_sql(l_str, ',');
end;
/
PL/SQL procedure successfully completed
-- 直接增加到2001个元素,发现f_str2list_sql()内部报错,说明构造字符串的成功传入,但是函数不能处理
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 2001;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_str := substr(l_str, 2);
l_list := f_str2list_sql(l_str, ',');
end;
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at "T2.F_STR2LIST_SQL", line 8
ORA-06512: at line 13
备注:ORA-01460的描述很不清楚,其实这里就是varchar2超长
测试比较两种方式在不同数据量下的性能
----------------------------------------------------
-- performance test
declare
l_list my_tk_str_tab_type := my_tk_str_tab_type();
l_str varchar2(32767) := '';
l_max pls_integer := 2000;
begin
-- construct string
for i in 1 .. l_max loop
l_str := l_str || ',' || 'a';
end loop;
l_str := substr(l_str, 2);
-- warm up before actually calculation
l_list := f_str2list_sql(l_str, ',');
l_list := f_str2list_pls(l_str, ',');
-- begin calc and diff
my_rs.rs_start;
-- 1. pls version
l_list := f_str2list_pls(l_str, ',');
my_rs.rs_middle;
-- 2. sql version
l_list := f_str2list_sql(l_str, ',');
my_rs.rs_stop();
end;
/
我们分别测试当元素个数为100, 200, 500, 1000, 2000的情况。每种情况进行3-5次然后取平均
元素个数 | PL/SQL 运行时间 (1/100 second) | SQL 运行时间 (1/100 second) | PCT |
100 | 1 | 1 | 100% |
200 | 1 | 1 - 2 | 50% - 100% |
500 | 1 - 2 | 2 - 3 | 30% - 50% |
1000 | 1 | 4 | 25% |
2000 | 2 | 10 | 20% |
总结:
- SQL版本的书写简便,并且可以脱离PL/SQL环境直接使用(单条SQL进行行列转换)。
- SQL版本只能处理长度小于4000的字符串,实际最大只能包含2000个元素。PL/SQL版本可以处理长度小于32767的字符串。
- 小数据量情况下,两者性能相当。随着数据量增大,PL/SQL版本性能明显占优。
posted on 2012-01-31 12:01 wait4friend 阅读(1659) 评论(0) 编辑 收藏 举报