首页 > 代码库 > 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 带回滚的存储过程
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。