1.C#代码：
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;
}
}
2.存储过程：
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;
3.自定义类型：
--创建自定义类型
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