首页 > 代码库 > Oracle 带回滚的存储过程

Oracle 带回滚的存储过程

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;

 

Oracle 带回滚的存储过程