自己实现的oracle字符串split方法

create or replace package my_utils is
   type split_array is table of varchar2(4000) index by binary_integer;
   --字符串分割
   function fn_split(
            p_str in varchar2,
            p_separator in varchar2
   ) return split_array;
end my_utils;
/
create or replace package body my_utils is
   --字符串分割
   function fn_split(
            p_str in varchar2,
            p_separator in varchar2
   ) return split_array is
     v_str varchar2(4000);
     v_split_array split_array;
     v_index binary_integer := 0;
   begin
     if p_str is null or p_separator is null then
       return v_split_array;
     end if;
     v_str := p_str;
     --先trim前后的p_separator
     if instr(v_str,p_separator,1) = 1 then --如果以p_separator开头则ltrim
       v_str := ltrim(v_str,p_separator);
     end if;
     if instr(v_str,p_separator,-1) = (length(v_str) - length(p_separator) + 1) then ----如果以p_separator结尾则rtrim
       v_str := rtrim(v_str,p_separator);
     end if;
     if length(v_str) = 0 then
       return v_split_array;
     end if;
     while instr(v_str,p_separator) != 0 loop
       if length(substr(v_str,1,instr(v_str,p_separator) - 1)) != 0 then--如果两个p_separator相邻则不加入v_split_array中
         v_index := v_index + 1;
         v_split_array(v_index) := substr(v_str,1,instr(v_str,p_separator) - 1);
       end if;
       v_str := substr(v_str,instr(v_str,p_separator) + length(p_separator));
     end loop;
     if length(v_str) != 0 then
       v_split_array(v_index + 1) := v_str;
     end if;
     return v_split_array;
   end fn_split;
end my_utils;
/

示例：

declare 
  v_split_array my_utils.split_array;
begin
  v_split_array := my_utils.fn_split('java.lang..String.','.');
  dbms_output.put_line('count : ' || v_split_array.count);
  for i in 1 .. v_split_array.count loop
    dbms_output.put_line('index['||i||'] = ' || v_split_array(i));
  end loop;
end;

输出：
count : 3
index[1] = java
index[2] = lang
index[3] = String