oracle中的split

网上找到一个oracle的split方法，

1.创建一个array的type

CREATE OR REPLACE Type t_Array Is Varray(2000) Of Varchar2(4000);

/

 

2.创建一个function

CREATE OR REPLACE FUNCTION F_SPLITSTR(Str       In Varchar2,
                                      Separator In Varchar2) RETURN t_Array -- 返回值的数据类型
 IS
  Len          Number;
  Currentnum   Number;
  Currentindex Number;
  Strs         t_Array;
  Ichar        Number;
BEGIN
  Len          := Length(Str);
  Currentnum   := 1;
  Currentindex := 1;
  Strs         := t_Array();
  For i In 1 .. Len Loop
    If Substr(Str, i, 1) = Separator Then
      Strs.Extend(1);
      Strs(Currentindex) := Substr(Str, Currentnum, i - Currentnum);
      Currentindex := Currentindex + 1;
      Currentnum := i + 1;
    End If;
    Ichar := i;
  End Loop;
  Strs.Extend(1);
  If Currentindex = 1 Then
    Strs(1) := Str;
  Else
    Strs(Currentindex) := Substr(Str, Currentnum, Ichar - Currentnum + 1);
  End If;
  Return Strs;
END;
/

一个外国人写的更少

create or replace function tabstr (
      p_str in varchar2,
      p_sep in varchar2 default ','
      )
  return t_array
  is
      l_str long := p_str || p_sep;
      l_tabstr t_array := t_array();
  begin
      while l_str is not null loop
          l_tabstr.extend(1);
          l_tabstr(l_tabstr.count) := rtrim(substr(
                  l_str,1,instr(l_str,p_sep)),p_sep);
          l_str := substr(l_str,instr(l_str,p_sep)+1);
      end loop;
      return l_tabstr;
  end;

/

 

3.在程序块中应用（该例是个触发器）

create or replace trigger t_test
before insert on test
for each row
Declare
  Str_Varray t_Array;
BEGIN
  Str_Varray := f_Splitstr(:new.userid, ',');
  For y In 1 .. Str_Varray.Count Loop
    insert into test2
      (id,
       varappid,
       varoriginator,
       varappuserid,
       varapptasktype,
       varapptaskid,
       varurl,
       vartaskdesc,
       istatus,
       ipriority,
       varactivetime)
    values
      (SEQ.Nextval,
       26,
       :new.suid,
       Str_Varray(y) || ';',
       28,
       :new.id,
       :new.url,
       :new.tname,
       0,
       4,
       to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  end loop;
END;
/