oracel中字符串分割成集合详解

文章分享一篇关于自己的学习笔记,oracel中字符串分割成集合详解有需要学习的同学可以看看。

首先分别使用两种方式构造两个函数

代码如下

复制代码

-- 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   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    where instr(p_separator || a.str, p_separator, 1, rn) > 0;   return l_list; end; /

 

 

确认两种方法完成同样的功能

 

----------------------------------------------------

代码如下

复制代码

-- 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 ed 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版本性能明显占优