• 向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         }
    示例
  • 相关阅读:
    5.线性回归算法
    作业14 15 手写数字识别-小数据集
    作业13 14 深度学习-卷积
    作业12 13-垃圾邮件分类2
    作业11 12.朴素贝叶斯-垃圾邮件分类
    作业10:11.分类与监督学习,朴素贝叶斯分类算法
    作业9、主成分分析
    作业8、特征选择
    作业7.逻辑回归实践
    作业6.逻辑归回
  • 原文地址:https://www.cnblogs.com/hujiapeng/p/4489753.html
Copyright © 2020-2023  润新知