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;

----------------------------------------------------------------

方法中使用数组

    Str_Varray  t_Array;
    Str_Varray := f_Splitstr(p_xianlu_id, ',');
   For y In 1 .. Str_Varray.Count Loop
       select * from table1 where aa=Str_Varray(y)
  end loop;
动态执行sql语句

    v_sqlstr :='delete from table1';
    execute immediate v_sqlstr;

批量更新

UPDATE table T1 SET
(T1.aa,T1.ab)=
(SELECT T2.ca,T2.cb
FROM table2 T2
WHERE T2.ca='aaa' and  T2.aa_id=t1.aa_id)
where exists
(select 1 from table2 T2 where t2.aa_id=t1.aa_id)