一年又快过去了,,,,12月,广州的12月,一天冷,一天热,一周内体验四季!
先分享一个,oracle中比较常用的,根据分隔符拆分字符串为多行结果集的sql写法,平时对于不是特别长的字符串的拆分,用着还是挺方便的。代码及查询的效果如下:
select regexp_substr('abc,def,ghi,jkl', '[^,]+', 1, level) c1
from dual
connect 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;