1、首先用PL/SQL创建package
1 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);--单个语句中不能再使用分号(;)结束SQL语句，否则会报ORA-00911: 无效字符异常 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 } |
