1）
using Oracle.DataAccess.Types;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Oracle.DataAccess.Client;

namespace YY.SmsPlatform.OracleDataSource

{

    public class HotStandby_RechargeType : IOracleCustomType

    {

        public void FromCustomObject(OracleConnection con, IntPtr pUdt)

        {

            OracleUdt.SetValue(con, pUdt, "USERNAME", this.UserName);

            OracleUdt.SetValue(con, pUdt, "MSGTYPE", this.MsgType);

            OracleUdt.SetValue(con, pUdt, "VAL", this.Val);

        }

        public void ToCustomObject(OracleConnection con, IntPtr pUdt)

        {

            this.UserName = (string)OracleUdt.GetValue(con, pUdt, "USERNAME");

            this.MsgType = (int)OracleUdt.GetValue(con, pUdt, "MSGTYPE");

            this.Val = (int)OracleUdt.GetValue(con, pUdt, "VAL");

        }

        [OracleObjectMapping("USERNAME")]

        public string UserName { get; set; }

        [OracleObjectMapping("MSGTYPE")]

        public int MsgType { get; set; }

        [OracleObjectMapping("VAL")]

        public int Val { get; set; }

    }

    [OracleCustomTypeMappingAttribute("HOTSTANDBY_RECHARGE")]

    public class HotStandby_RechargeFactory : IOracleCustomTypeFactory

    {

        public IOracleCustomType CreateObject()

        {

            return new HotStandby_RechargeType();

        }

    }

    [OracleCustomTypeMapping("HOTSTANDBY_RECHARGE_ARRAY")]

    public class HotStandby_RechargeArrayFactory : IOracleArrayTypeFactory

    {

        public Array CreateArray(int numElems)

        {

            return new HotStandby_RechargeFactory[numElems];

        }

        public Array CreateStatusArray(int numElems)

        {

            return null;

        }

    }

}
2）
//调用存储过程
public bool UserAmountChange(Dictionary<string, long> fee, Dictionary<string, long> recharge)
{
    List<HotStandby_RechargeType> f = GetRechargeTypeArray(fee);
    List<HotStandby_RechargeType> r = GetRechargeTypeArray(recharge);
    using (OracleConnection conn = this.CreateConnection())
    using (OracleCommand cmd = conn.CreateCommand())
   {
       conn.Open();
       cmd.CommandText = "Proc_HotStandbyQuotaChange";
       cmd.CommandType = CommandType.StoredProcedure;
       var op = new OracleParameter { ParameterName = "result", OracleDbType = OracleDbType.Int32, Direction = ParameterDirection.Output,Value=null };
       cmd.Parameters.AddRange(new OracleParameter[] {
       new OracleParameter {ParameterName= "FeeDeduction", OracleDbType=OracleDbType.Array,Direction=ParameterDirection.Input,UdtTypeName= "HOTSTANDBY_RECHARGE_ARRAY", Value=f.ToArray() },
       new OracleParameter {ParameterName= "Recharge", OracleDbType = OracleDbType.Array, Direction = ParameterDirection.Input, UdtTypeName = "HOTSTANDBY_RECHARGE_ARRAY", Value = r.ToArray() },
       op });//HotStandby_Recharge_Array
       cmd.ExecuteNonQuery();
       int num = Convert.ToInt32(op.Value.ToString());
       return num == 0;
   }
}

create or replace procedure Proc_HotStandbyQuotaChange

(

          FeeDeduction in HotStandby_Recharge_Array,

          Recharge in HotStandby_Recharge_Array,

          result  out integer

)as

 cursor f_cursor is select * from table(FeeDeduction) ;

 cursor r_cursor is select * from table(Recharge);

 v_userid integer:=0;

 f_row f_cursor%rowtype;

 r_row r_cursor%rowtype;

begin

  open f_cursor;--打开游标

  --  fetch f_cursor into f_row ;

      loop

         fetch f_cursor into f_row ;

        --让游标指针往下移动

        exit when f_cursor%notfound;

        update user_amountinfo t set t.amount=t.amount-(f_row.Val) where t.userid=(select c.userid from base_userinfo c where c.username=f_row.UserName) and t.msgtype=f_row.MsgType;

--        fetch f_cursor into f_row ;

      end loop;

      close f_cursor;

   open r_cursor;

     loop

       fetch r_cursor into r_row;

       exit when r_cursor%notfound;

       select nvl(max(t.userid),0) into v_userid from user_amountinfo t where t.userid = (select u.userid from base_userinfo u where u.username =r_row.UserName) and t.msgtype = r_row.MsgType;

      if (v_userid>0)

        then

          update user_amountinfo t set t.amount=t.amount-(r_row.Val),t.addtime=SYSDATE(),t.rechargeamount=t.rechargeamount-(r_row.Val) where t.userid=v_userid and t.msgtype=r_row.MsgType;

        elsif (v_userid<=0)

          then

            select t.userid into v_userid from base_userinfo t where t.username=r_row.UserName;

          insert into user_amountinfo(userid,amount,msgtype,addtime,rechargeamount)  values(v_userid,r_row.Val,r_row.MsgType,Sysdate(),r_row.Val);

        end if;

     end loop;

     close r_cursor;

     commit;

     result:=sqlcode;

end Proc_HotStandbyQuotaChange;

--创建自定义类型
CREATE OR REPLACE TYPE HotStandby_Recharge

is object(

   UserName varchar2(50),

   MsgType number,

   Val     number

)；
--创建自定义表类型
CREATE OR REPLACE TYPE HOTSTANDBY_RECHARGE_ARRAY as table of HotStandby_Recharge