Oracle自定义类型在C#中调用示例

1.C#代码：

 public bool UserAmountChange(Dictionary<string, long> fee, Dictionary<string, long> recharge)
        {
            var f = GetRechargeTypeArray(fee);
            var 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;
            }
        }

2.定义与数据库对应类型：

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

3.数据库类型定义及表类型定义

--创建表
CREATE OR REPLACE TYPE HOTSTANDBY_RECHARGE_ARRAY as table of HotStandby_Recharge 
--创建类型
CREATE OR REPLACE TYPE HotStandby_Recharge
is object(
   UserName varchar2(50),
   MsgType integer,
   Val     integer
)

4.C#调用的存储过程

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 t.userid 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
          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;