create or replace procedure PROC_insertUserAmount

(

     userid  number,

     msgtype number,

     amountvalue number,

     financeType  number,

     createUserid number,

     msgId nvarchar2,

     remark nvarchar2,

     addtime date

)

is

     v_cnt number;

     v_beforevalue number;

     v_aftervalue number;

     v_userid number;

     v_msgtype number;

begin

  v_userid:=userid;

   v_msgtype:=msgtype;

   select count(*)into v_cnt from dual

       where exists (select 1 from user_amountinfo t where t.userid=v_userid and t.msgtype=v_msgtype);

   if  v_cnt>0 then

       update user_amountinfo set amount=amount+amountvalue  where user_amountinfo.userid=v_userid and user_amountinfo.msgtype=v_msgtype;

   else

       insert into user_amountinfo (userid,msgtype,amount)values(userid,msgtype,amountvalue);

   end if;

   select amount-amountvalue,amount into v_beforevalue,v_aftervalue from user_amountinfo t where t.userid=v_userid and t.msgtype=v_msgtype;

   insert into user_amountinfodetails(userid,msgtype,beforevalue,amountvalue,aftervalue,financetype,createUserid,msgid,remark,addtime)

       values(userid,msgtype,v_beforevalue,amountvalue,v_aftervalue,financetype,createUserid,msgid,remark,addtime);

   if(financetype<>2)then

       insert into user_amountinforecharges(id,userid,msgtype,beforevalue,amountvalue,aftervalue,financetype,createUserid,msgid,remark,addtime)

       values(seq_user_amountinfodetails_id.currval,userid,msgtype,v_beforevalue,amountvalue,v_aftervalue,financetype,createUserid,msgid,remark,addtime);

   end if;

   if(financetype=1) then--类型为充值

        update user_amountinfo set rechargeamount=rechargeamount+amountvalue  where user_amountinfo.userid=v_userid and user_amountinfo.msgtype=v_msgtype;

   end if;

   commit;

exception--出现异常，回滚事务，抛出异常

  when others then

    rollback;

    raise;

end;