向Oracle中传入数组，批量执行SQL语句

create or replace package excuteBatchOperate 2 as 3 type sqlStr_Array is table of varchar2 ( 1000 ) index by binary_integer; 4 procedure excuteBulkData(sqlStr in sqlStr_Array); 5 end excuteBatchOperate;

2、在packagebody内创建存储过程

  1   create   or   replace   package body excuteBatchOperate
   2   as 
  3   procedure  excuteBulkData(sqlStr  in   sqlStr_Array)
   4     as 
  5     begin 
  6       for  i  in   1 ..sqlStr. count   loop
   7          execute   immediate sqlStr(i);
   8       end   loop;
   9     end   excuteBulkData;
  10     end  excuteBatchOperate;

3、.Net demo如下，可以去掉注释，修改SQL语句测试

  1    public   static   int  excuteBulkData(IList< string >  list)
   2           {
   3               using  (ODAC.OracleConnection conn =  new   ODAC.OracleConnection(connStr))
   4               {
   5                   using  (ODAC.OracleCommand comm =  conn.CreateCommand())
   6                   {
   7                       //  IList<string> list = new List<string>();
   8                       //  list.Add("insert into parts1(pname) values(‘sadfsa‘)");
   9                       //  list.Add("insert into parts1(pnum,pname) values(22222,‘rrrrrr‘)"); 
 10                       conn.Open();
  11                      comm.CommandType =  CommandType.StoredProcedure;
  12                      comm.CommandText =  "  excuteBatchOperate.excuteBulkData  "  ;
  13                      ODAC.OracleParameter Param1 =  new 
 14  ODAC.OracleParameter( @"  v_string  "  , ODAC.OracleDbType.Varchar2);
  15                      Param1.Direction =  ParameterDirection.Input;
  16                      Param1.CollectionType =  ODAC.OracleCollectionType.PLSQLAssociativeArray;
  17                      Param1.Value =  list.ToArray();
  18                       comm.Parameters.Add(Param1);
  19                      return   comm.ExecuteNonQuery();
  20                   }
  21               }
  22          }

示例

 

向Oracle中传入数组，批量执行SQL语句

标签：

查看更多关于向Oracle中传入数组，批量执行SQL语句的详细内容...