PL/SQL中操作字符串的工具方法

在平常的工作中，经常会对字符串进行操作，在此写简单的工具类。
目前包括：
1：fn_split，将由分隔符组成的字符转换成集合类型。
2：fn_contact，将集合里的字符串拼接成一个字符串。


创建PKG定义：

create or replace package character_util is

  --构建集合类型 DBMS_UTILITY
  TYPE t_Array IS TABLE OF VARCHAR2(4000) index by binary_integer;

  --默认的分隔符
  C_SEPARATOR CONSTANT VARCHAR2(1 CHAR) := ',';

  /**
    -- Purpose 实现类似java的split函数，返回集合类型
    -- parameter pi_str需要操作的字符串
    -- parameter pi_separator 分隔符
  **/

  function fn_split(pi_str       Varchar2,
                    pi_separator Varchar2 default C_SEPARATOR) RETURN t_Array;
  /**
    -- Purpose 实现将集合中的字符用连接符串联，返回字符串类型
    -- parameter pi_collect 集合
    -- parameter pi_conj_char 连接符
  **/
  function fn_contact(pi_collect   t_Array,
                      pi_conj_char varchar2 default C_SEPARATOR)
    return varchar2;

end character_util;

创建BODY：

create or replace package body character_util is

  FUNCTION fn_split(pi_str       Varchar2,
                    pi_separator Varchar2 default C_SEPARATOR) RETURN t_Array IS
    --分隔符在字符串中的位置
    idx Number;
    --数组的索引
    currentindex Number := 1;
    --返回值
    l_ret t_Array;
    --临时变量
    v_str varchar2(32767) := pi_str;
  BEGIN
  
    IF pi_str IS NOT NULL AND pi_separator IS NOT NULL THEN
      --存在分隔符
      IF INSTR(v_str, pi_separator) <> 0 THEN
        LOOP
          EXIT WHEN v_str IS NULL;
          EXIT WHEN INSTR(v_str, pi_separator) = 0;
          idx := INSTR(v_str, pi_separator);
          --截取字符串赋值给对应的数组
          l_ret(currentindex) := SUBSTR(v_str, 0, idx - 1);
          currentindex := currentindex + 1;
          --更新字符串
          v_str := SUBSTR(v_str, idx + 1);
        END LOOP;
      END IF;
    END IF;
    --如果分隔符为空，则直接返回字符串
    IF v_str IS NOT NULL THEN
      l_ret(currentindex) := v_str;
    END IF;
  
    Return l_ret;
  
  END fn_split;

  function fn_contact(pi_collect   t_Array,
                      pi_conj_char varchar2 default C_SEPARATOR)
    return varchar2 is
    --返回值
    l_ret varchar2(50 char);
  begin
  
    --遍历集合
    for i in pi_collect.first .. pi_collect.count loop
      --进行字符串拼接
      --当拼接后的长度不会超过返回变量所定义的长度时才允许往后添加
      --暂时不知如何获取l_ret所定义的长度，故写硬代码50在此
      if length(l_ret || pi_conj_char || pi_collect(i)) <= 50 then
        if i > 1 then
          l_ret := l_ret || pi_conj_char || pi_collect(i);
        else
          l_ret := pi_collect(i);
        end if;
      end if;
    
    end loop;
  
    return l_ret;
  end fn_contact;

end character_util;

测试代码：

declare

  --fn_contact函数的入参
  l_collect scott.character_util.t_Array;
  --fn_contact函数的返回值
  l_char varchar2(32767);
  --fn_split函数的返回值
  l_ret_collect scott.character_util.t_Array;

begin

  --准备数据
  select t.ename bulk collect into l_collect from scott.emp t;
  --调用fn_contact，获取返回值
  l_char := scott.character_util.fn_contact(l_collect);
  --打印拼接好的字符串
  dbms_output.put_line('fn_contact的效果；');
  dbms_output.put_line(l_char);

  --将上面的返回值作为fn_split的入参
  l_ret_collect := scott.character_util.fn_split(l_char);

  dbms_output.put_line('');
  dbms_output.put_line('fn_split的效果；');
  --遍历fn_split返回的集合数据
  for i in l_ret_collect.first .. l_ret_collect.count loop
    dbms_output.put_line('Index:' || i || '=' || l_ret_collect(i));
  end loop;

end;

执行效果：