Oracle 带回滚的存储过程
时间：2021-07-01 10:21:17
帮助过：17人阅读
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 带回滚的存储过程
标签：cep val type 回滚事务 rac end after begin charge
