ORACLE中使用数组

 Type t_Array Is Varray(2000) Of Varchar2(4000);
 v_Varray    t_Array;
 
  --将字符串转换成为数组        --数组解析函数摘的，不应该每个字符遍历，只作启示
 Function f_Splitstr(Str In Varchar2, Separator In Varchar2) Return t_Array;

 --返回数组
  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 f_Splitstr;


方法中使用数组

    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)