本文共 3908 字，大约阅读时间需要 13 分钟。
一年又快过去了，，，，12月，广州的12月，一天冷，一天热，一周内体验四季！
先分享一个，oracle中比较常用的，根据分隔符拆分字符串为多行结果集的sql写法，平时对于不是特别长的字符串的拆分，用着还是挺方便的。代码及查询的效果如下：
select regexp_substr('abc,def,ghi,jkl', '[^,]+', 1, level) c1 from dualconnect by level <= regexp_count('abc,def,ghi,jkl', '[^,]+') 
然后，前些日子帮朋友调试一个存储过程的时候，发现传了个贼长的字符串进来，用上面的正则 + connect by的方法处理起来就比较慢，而且也不支持超长字符串的拆分，百度折腾了下，决定写个函数处理。
一、解决思路
1、利用管道函数pipelined和自定义的table类型，通过函数拆分字符串并插入到结果集中，直接返回拆分后的结果集。
2、然后，通过clob类型传入，解决超长字符串的问题，在函数中分段进行截取，拆分。
二、代码实现
1、先建一个自定义的table类型，数据类型为varchar2
CREATE OR REPLACE TYPE type_table_varchar2 IS TABLE OF VARCHAR(4000);
2、函数处理
create or replace function f_split(as_clob clob, delimiter varchar2) return type_table_varchar2 pipelined is ls_str1 varchar2(8000); ln_cnt number(8); ls_str2 varchar2(2000); x number(8); y number(8);begin -- 算一下要循环几次 ln_cnt := ceil(length(as_clob) / 4000); -- 开始循环获取 for i in 1 .. ln_cnt loop -- 取出4000个字符 ls_str1 := to_char(substr(as_clob, (4000 * (i - 1)) + 1, 4000)); -- 定位到1 x := 1; y := 1; -- 开始循环截取 loop -- 定位分隔符 x := instr(ls_str1, delimiter, y); -- 如果存在分隔符，那就从现在的位置截取到分隔符前一位 if x > 0 then -- 截取并去空格 ls_str2 := ls_str2 || trim(substr(ls_str1, y, x - y)); -- 如果长度大于0，那就是有效的字符串 if ls_str2 is not null then -- 取出的单条字符串插入集合，并重置ls_str2为空 pipe row(ls_str2); ls_str2 := ''; end if; -- 查找开始位置往当前定位后挪1位 y := x + 1; else -- 当前位置往后面不存在分隔符的话，取出后面的字符串，退出本次循环 ls_str2 := ls_str2 || trim(substr(ls_str1, y, 4000 - y + 1)); -- dbms_output.put_line('后面没分隔符了，取出：' || ls_str2); exit; end if; end loop; end loop; -- 最后的剩余部分处理 if ls_str2 is not null then pipe row(ls_str2); end if; return;end f_split; 三、测试
1、使用方法就很简单啦，RT
因为有进行前后去空格和空字符串不插入处理，如果不需要这个细节的自行修改咯。
2、试下超长字符串，一个差不多14400长的clob，搞它一搞
拆分和插入大概用了0.05s，效率还是挺不错的。
-------------------------------------------------------------------------------------------
2020年3月14，
修改了上面的：ls_str2 := ls_str2 || trim(substr(ls_str1, y, 4000 - y + 1));
另外，因修改另外一个程序，涉及到，拆分后的每一个字符串，有可能超过4000，所以，加上一个处理成clob的方法。
类型定义：CREATE OR REPLACE type type_table_clob IS TABLE OF CLOB;
函数：
/** * 拆分字符串，返回table(clob) * 参数： * as_clob 要拆分的字符串 * as_delimiter 分隔符 */ function f_split(as_clob clob, as_delimiter varchar2) return type_table_clob pipelined is ls_str1 varchar2(8000); ln_cnt number(8); ls_str2 clob; x number(8); y number(8); begin -- 算一下要循环几次 ln_cnt := ceil(length(as_clob) / 4000); -- 开始循环获取 for i in 1 .. ln_cnt loop -- 取出4000个字符 ls_str1 := to_char(substr(as_clob, (4000 * (i - 1)) + 1, 4000)); -- dbms_output.put_line('开始一次->'||ls_str1); -- 定位到1 x := 1; y := 1; -- 开始循环截取 loop -- 定位分隔符 x := instr(ls_str1, as_delimiter, y); -- 如果存在分隔符，那就从现在的位置截取到分隔符前一位 if x > 0 then -- 截取并去空格 ls_str2 := ls_str2 || trim(substr(ls_str1, y, x - y)); -- 如果长度大于0，那就是有效的字符串 if ls_str2 is not null then -- 取出的单条字符串插入集合，并重置ls_str2为空 pipe row(ls_str2); -- dbms_output.put_line(ls_str2); ls_str2 := ''; end if; -- 查找开始位置往当前定位后挪1位 y := x + 1; else -- 当前位置往后面不存在分隔符的话，取出后面的字符串，退出本次循环 ls_str2 := ls_str2 || trim(substr(ls_str1, y, 4000 - y + 1)); -- dbms_output.put_line('后面没分隔符了，取出：' || ls_str2); exit; end if; end loop; -- dbms_output.put_line('结束一次->'||ls_str2); end loop; -- 最后的剩余部分处理 if ls_str2 is not null then pipe row(ls_str2); --dbms_output.put_line(ls_str2); end if; return; end f_split;
转载地址：http://umkgf.baihongyu.com/