oracle中的split

时间：2010-07-01 来源：yuxiang-liu

网上找到一个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;
/