oracle执行批量sql语句,向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 list)

2 {

3 using (ODAC.OracleConnection conn = new ODAC.OracleConnection(connStr))

4 {

5 using (ODAC.OracleCommand comm = conn.CreateCommand())

6 {

7 //IList list = new List();

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