oracel中字符串分割成集合详解-mysql教程-PHP中文网

首页

数据库

mysql教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:49 PM

字符串分割

文章分享一篇关于自己的学习笔记,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

100%

200

1 - 2

50% - 100%

500

1 - 2

2 - 3

30% - 50%

1000

25%

2000

20%

总结：

SQL版本的书写简便，并且可以脱离PL/SQL环境直接使用（单条SQL进行行列转换）。

SQL版本只能处理长度小于4000的字符串，实际最大只能包含2000个元素。PL/SQL版本可以处理长度小于32767的字符串。

小数据量情况下，两者性能相当。随着数据量增大，PL/SQL版本性能明显占优

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

mySQL：字符串数据类型和枚举？May 13, 2025 am 12:05 AM

mysqloffersechar，varchar，text，and denumforstringdata.usecharforfixed Lengttrings，varcharerforvariable长度，文本forlarger文本，andenumforenforcingDataAntegrityWithaEtofValues。

mysql blob：如何优化斑点请求May 13, 2025 am 12:03 AM

优化MySQLBLOB请求可以通过以下策略：1.减少BLOB查询频率，使用独立请求或延迟加载；2.选择合适的BLOB类型（如TINYBLOB）；3.将BLOB数据分离到单独表中；4.在应用层压缩BLOB数据；5.对BLOB元数据建立索引。这些方法结合实际应用中的监控、缓存和数据分片，可以有效提升性能。

将用户添加到MySQL：完整的教程May 12, 2025 am 12:14 AM

掌握添加MySQL用户的方法对于数据库管理员和开发者至关重要，因为它确保数据库的安全性和访问控制。1)使用CREATEUSER命令创建新用户，2)通过GRANT命令分配权限，3)使用FLUSHPRIVILEGES确保权限生效，4)定期审计和清理用户账户以维护性能和安全。

掌握mySQL字符串数据类型：varchar vs.文本与charMay 12, 2025 am 12:12 AM

chosecharforfixed-lengthdata，varcharforvariable-lengthdata，andtextforlargetextfield.1）chariseffity forconsistent-lengthdatalikecodes.2）varcharsuitsvariable-lengthdatalikenames，ballancingflexibilitibility andperformance.3）

MySQL：字符串数据类型和索引：最佳实践May 12, 2025 am 12:11 AM

在MySQL中处理字符串数据类型和索引的最佳实践包括：1)选择合适的字符串类型，如CHAR用于固定长度，VARCHAR用于可变长度，TEXT用于大文本；2)谨慎索引，避免过度索引，针对常用查询创建索引；3)使用前缀索引和全文索引优化长字符串搜索；4)定期监控和优化索引，保持索引小巧高效。通过这些方法，可以在读取和写入性能之间取得平衡，提升数据库效率。

See all articles