• C# Passing Associative Arrays to PL/SQL Code


    CREATE OR REPLACE PACKAGE ProductsPackage IS
      TYPE DecimalArray IS TABLE OF DECIMAL INDEX BY BINARY_INTEGER;
      TYPE StringArray IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
      PROCEDURE proc_UpdateMultiplePrices(ProdPrices IN DecimalArray, ProdNames IN 
      StringArray);
    END ProductsPackage;

    /

    CREATE OR REPLACE PACKAGE BODY ProductsPackage IS
      PROCEDURE proc_UpdateMultiplePrices(ProdPrices IN DecimalArray, ProdNames IN 
      StringArray)   
     IS
     BEGIN
        FOR i IN 1..ProdNames.LAST
      LOOP
    UPDATE Products SET Price = Price + ProdPrices(i) WHERE Name = 
     ProdNames(i);
      END LOOP;
     END;
    END ProductsPackage;

    /

    private void btnUpdateMultiplePrices_Click(object sender, EventArgs e)
    {
      String _connstring = "Data Source=localhost/NEWDB;User 
      Id=EDZEHOO;Password=PASS123;";
     try
     { OracleConnection _connObj = new OracleConnection(_connstring);
      _connObj.Open();
      OracleCommand _cmdObj = _connObj.CreateCommand();
      _cmdObj.CommandText = "ProductsPackage.proc_UpdateMultiplePrices";
      _cmdObj.CommandType = CommandType.StoredProcedure;
      OracleParameter _priceParam = new OracleParameter();
      _priceParam.ParameterName = "ProdPrices";
      _priceParam.OracleDbType = OracleDbType.Decimal;
      _priceParam.Direction = ParameterDirection.Input;
      _priceParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Decimal [] decArray= new Decimal[3];
      decArray[0] = 100;
      decArray[1] = 300;
      decArray[2] = 500;
      _priceParam.Value = decArray;
      _cmdObj.Parameters.Add(_priceParam);
      OracleParameter _NameParam = new OracleParameter();
      _NameParam.ParameterName = "ProdNames";
      _NameParam.OracleDbType = OracleDbType.Varchar2;
      _NameParam.Direction = ParameterDirection.Input;
      _NameParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      String[] stringArray = new String[3];
      stringArray[0] = "Engine";
      stringArray[1] = "Windshield";
      stringArray[2] = "Rear Lights";
      _NameParam.Value = stringArray;
      _cmdObj.Parameters.Add(_NameParam);
      _cmdObj.ExecuteNonQuery();  MessageBox.Show("All products updated!");
     
                    _connObj.Close();
                    _connObj.Dispose();
                    _connObj = null;
     }
      catch (Exception ex)
     {
      MessageBox.Show(ex.ToString());
     }
    }

  • 相关阅读:
    hive从入门到放弃(一)——初识hive
    LeetCode 2040. Kth Smallest Product of Two Sorted Arrays
    LeetCode weekly contest 278 (amazon pay)
    LeetCode Weekly Contest 281
    《前端运维》一、Linux基础06Shell流程控制
    《前端运维》一、Linux基础05Shell运算符
    一比一还原axios源码(二)—— 请求响应处理
    一比一还原axios源码(八)—— 其他功能
    一比一还原axios源码(三)—— 错误处理
    《前端运维》一、Linux基础04Shell变量
  • 原文地址:https://www.cnblogs.com/kingwangzhen/p/1795114.html
Copyright © 2020-2023  润新知