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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle自定义类型在C#中调用示例，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4198字，纯文字阅读大概需要6分钟。

内容图文

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;

 

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

标签：存储过程   hot   output   factory   typename   创建类型   ora   put   游标   

本文系统来源：http://www.cnblogs.com/rengke2002/p/7462491.html

内容总结

以上是互联网集市为您收集整理的Oracle自定义类型在C#中调用示例全部内容，希望文章能够帮你解决Oracle自定义类型在C#中调用示例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---