Oracle 10g数据库分隔符函数实现(原创)

    工作中有时碰到需要在数据库中进行类似Java中split(",")生成数组方法，
希望也在数据库应用开发中也有这样的方法，前段时间查了Oralce官方网站关于管道函数的用法
参照帮助手册自己也写了一个分隔符函数返回数据集的函数，记录并学习...

 (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0)

 

--1
create or replace type split_type as table of varchar2(32676);
--2
create or replace function fn_split(in_var varchar2,in_del varchar2,in_flag varchar2)
 return split_type PIPELINED 
as
  temp_str varchar2(32767):=in_var;
  temp_delimiter  varchar2(20):=',';--默认为逗号
  temp_index number:=0;            --分隔符的索引位置             
  temp_flag varchar2(20):='0';--默认没有前、后面逗号内容
  temp_rec number:=0;         --记录最后一位是否存在分隔符号
  --通过管道分隔函数实现分组，以指定的分隔符进行划分数据
  --标识是否具有首尾分隔符号数据出现数值 
  --默认为不出现 0不出现 1都出现 2前面出现 3后面出现
  --created by zxb 2008-10-31
begin
     temp_delimiter:=in_del;
     temp_flag:=in_flag;
     if length(temp_delimiter)=0 or temp_delimiter is null then
        temp_delimiter:=',';
     end if;
     if length(in_flag)=0 or in_flag is null then
        temp_flag:='0';
     end if;
     if instr(temp_str,temp_delimiter,-1)=length(temp_str) then
         temp_rec:=1; 
     end if;
     if(temp_flag='0') or (temp_flag<>'1' and temp_flag<>'2' and temp_flag<>'3') then
          if instr(temp_str,temp_delimiter)=1 then
             temp_str:=substr(temp_str,2);
          end if;
          if temp_rec=1 then
             temp_str:=substr(temp_str,1,length(temp_str)-1);
          end if;
          while instr(temp_str,temp_delimiter)>=0 loop
           temp_index:=instr(temp_str,temp_delimiter);
           if(temp_index>0) then
               pipe row(substr(temp_str,1,temp_index-1));
               temp_str:=substr(temp_str,temp_index+1);
           else
               pipe row(temp_str);
                exit;--10g,  return;--9i
           end if;
         end loop;  
      else
          if temp_flag='1' then 
               while instr(temp_str,temp_delimiter)>=0 loop
                 temp_index:=instr(temp_str,temp_delimiter);
                 if(temp_index>0) then
                    pipe row(substr(temp_str,1,temp_index-1));
                    temp_str:=substr(temp_str,temp_index+1);
                 else
                    pipe row(temp_str);
                    exit;--10g,  return;--9i
                 end if;
               end loop;
               if temp_rec=1 then
                  pipe row('');
               end if;
           else
               if temp_flag='2' then 
                  while instr(temp_str,temp_delimiter)>=0 loop
                    temp_index:=instr(temp_str,temp_delimiter);
                    if(temp_index>0) then
                       pipe row(substr(temp_str,1,temp_index-1));
                       temp_str:=substr(temp_str,temp_index+1);
                    else
                       pipe row(temp_str);
                        exit;--10g,  return;--9i
                    end if;
                  end loop;
               else
                  if instr(temp_str,temp_delimiter)=1 then
                     temp_str:=substr(temp_str,2);
                  end if;
                  while instr(temp_str,temp_delimiter)>=0 loop
                     temp_index:=instr(temp_str,temp_delimiter);
                     if(temp_index>0) then
                        pipe row(substr(temp_str,1,temp_index-1));
                        temp_str:=substr(temp_str,temp_index+1);
                     else
                        pipe row(temp_str);
                         exit;--10g,  return;--9i
                     end if;
                  end loop;
                  if temp_rec=1 then
                     pipe row('');
                  end if;  
               end if;
           end if;
      end if;
      exception
       when others then
       pipe row('');
end;
--3简易方法
create or replace function split_del(in_var varchar2,in_del varchar2)  
 return split_type PIPELINED 
 as
  temp_str varchar2(32767):=in_var;
  temp_delimiter  varchar2(20):=',';--默认为逗号
  temp_index number:=0;  
 begin
      temp_delimiter:=in_del;
      if length(temp_delimiter)=0 or temp_delimiter is null then
        temp_delimiter:=',';
      end if;
      while instr(temp_str,temp_delimiter)>=0 loop
         temp_index:=instr(temp_str,temp_delimiter);
         if(temp_index>0) then
            pipe row(substr(temp_str,1,temp_index-1));
            temp_str:=substr(temp_str,temp_index+1);
         else
            pipe row(temp_str);
             exit;--10g,  return;--9i
         end if;
       end loop;
      exception
       when others then
       pipe row(''); 
 end;
--测试代码
SQL> select * from table (fn_split(',abc,123,---,***,+++,2ss,',',',0));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
abc
123
---
***
+++
2ss
 
6 rows selected
 
SQL> select * from table (split_del(',abc,123,---,***,+++,2ss,',','));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
abc
123
---
***
+++
2ss
 
7 rows selected
 
SQL>