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());
}
}