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

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         }

示例

 

转载于:https://www.cnblogs.com/hujiapeng/p/4489753.html