1、首先用PL/SQL创建package
1 create or replace package excuteBatchOperate2 as3 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 = new14 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 }