Oracle 自定义函数实现split功能，支持超长字符串和clob类型的分隔

最新推荐文章于 2024-02-23 17:10:56 发布

原创最新推荐文章于 2024-02-23 17:10:56 发布 · 3.6k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #split #字符串拆分 #超长字符串

Oracle、sql、plsql 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中高效拆分超长字符串的方法，通过自定义函数和使用clob类型，解决了传统正则表达式方法在处理长字符串时的效率和长度限制问题。

一年又快过去了，，，，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;