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

1、首先用PL/SQL创建package

 create or replace package excuteBatchOperate

 as

  type sqlStr_Array is table of varchar2(1000) index by binary_integer;

  procedure excuteBulkData(sqlStr in sqlStr_Array);

  end excuteBatchOperate;

2、在packagebody内创建存储过程

 create or replace package body excuteBatchOperate

 as

 procedure excuteBulkData(sqlStr in sqlStr_Array)

   as

   begin

     for i in 1..sqlStr.count loop

        execute immediate sqlStr(i);--单个语句中不能再使用分号(;)结束SQL语句，否则会报ORA-00911: 无效字符异常

     end loop;

   end excuteBulkData;

   end excuteBatchOperate;

3、.Net demo如下，可以去掉注释，修改SQL语句测试

  public static int excuteBulkData(IList<string> list)

         {

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

             {

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

                 {

                     //IList<string> list = new List<string>();

                     //list.Add("insert into parts1(pname) values('sadfsa')");

                     //list.Add("insert into parts1(pnum,pname) values(22222,'rrrrrr')");

                     conn.Open();

                     comm.CommandType = CommandType.StoredProcedure;

                     comm.CommandText = "excuteBatchOperate.excuteBulkData";

                     ODAC.OracleParameter Param1 = new

 ODAC.OracleParameter(@"v_string", ODAC.OracleDbType.Varchar2);

                     Param1.Direction = ParameterDirection.Input;

                     Param1.CollectionType = ODAC.OracleCollectionType.PLSQLAssociativeArray;

                     Param1.Value = list.ToArray();

                     comm.Parameters.Add(Param1);

                    return comm.ExecuteNonQuery();

                 }

             }

         }

示例

向Oracle中传入数组，批量执行SQL语句的相关教程结束。