你的位置:首页 > 数据库

[数据库]向Oracle中传入数组,批量执行SQL语句

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     }

示例