向Oracle中傳入數組，批量執行SQL語句，oraclesql
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);
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 }
示例
