• 向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         }
    示例
  • 相关阅读:
    linux7系统进入单用户模式
    GoAccess日志分析工具
    yum总结
    分布式文件系统---GlusterFS
    【centos7】添加开机启动服务/脚本
    centos 7 服务初始化
    chrony软件
    [USACO4.1]麦香牛块Beef McNuggets 题解报告
    组合数的几种计算方法
    【ZJOI2005】沼泽鳄鱼 题解报告
  • 原文地址:https://www.cnblogs.com/hujiapeng/p/4489753.html
Copyright © 2020-2023  润新知