create or replace procedure PROC_SENT_MID_FEEVALUELOG is
cursor cur_tmp is
-----获取所有过期的记录
select g.f_fee_id,g.f_addvalue_number,g.f_fee_overtime from t_mid_sent_feevalue_log g
where g.f_fee_state =‘0’
and g.f_fee_overtime < to_char(sysdate,‘yyyyMMddhh24miss’)
begin
for cur_row in cur_tmp loop
begin
exit when cur_tmp%notfound;
----更新赠送日志表记录更新为过期
update t_mid_sent_feevalue_log t set t.f_fee_state =‘2’ where t.f_fee_id = cur_row.f_fee_id
----把充值券明细表锁定状态5 更新为初始化0
update t_fee_addvalue_new_log a set a.state = ‘0’ where a.state =‘5’
and a.addvalue_number =cur_row.f_addvalue_number
commit;
end;
end loop;
exception when others then
begin
rollback;
end;
end PROC_SENT_MID_FEEVALUELOG;
一个存储过程 学习用
最新推荐文章于 2024-07-26 00:24:43 发布