create or replace procedure PROC_1183_EXCHANGE is
exchange_tounuse number :=0 ; -------即将过期的兑换券数量-------
cursor guoqi_result is
---------------过期时间减去15天为即将过期时间--------------------
select * from t_1183_exchange_info f
where f.f_past_time >= to_char(sysdate,'yyyymmddhh24miss')
and to_char(to_date(f.f_past_time,'yyyymmddhh24miss') - interval '15' day ,'yyyymmdd') = to_char(sysdate,'yyyymmdd');
begin
for guoqi_index in guoqi_result
begin
exit when result_exchange%notfound;
--------出现即将过期的卷 就更新对应的即将过期的卷---------- -------1,查询之前即将过期的卷的数量--------
select nvl(hh.f_exchange_tounuse,0) into exchange_tounuse from t_1183_exchange_chance hh
where hh.f_exchange_type = guoqi_index.f_exchange_type
and hh.f_user_number = guoqi_index.f_user_number ; -------2,更新之前的过期卷数量加1-------------------
update t_1183_exchange_chance e
set e.f_exchange_tounuse = exchange_tounuse + 1
where e.f_exchange_type = guoqi_index.f_exchange_type
and e.f_user_number = guoqi_index.f_user_number ;
end;
end loop;
commit;
exception when others then
begin
rollback;
end;
end PROC_1183_EXCHANGE;
以上是存储过程
怎么写一个定时器让这个存储过程每天在指定的时间执行一次呢。。oracle定时器
exchange_tounuse number :=0 ; -------即将过期的兑换券数量-------
cursor guoqi_result is
---------------过期时间减去15天为即将过期时间--------------------
select * from t_1183_exchange_info f
where f.f_past_time >= to_char(sysdate,'yyyymmddhh24miss')
and to_char(to_date(f.f_past_time,'yyyymmddhh24miss') - interval '15' day ,'yyyymmdd') = to_char(sysdate,'yyyymmdd');
begin
for guoqi_index in guoqi_result
begin
exit when result_exchange%notfound;
--------出现即将过期的卷 就更新对应的即将过期的卷---------- -------1,查询之前即将过期的卷的数量--------
select nvl(hh.f_exchange_tounuse,0) into exchange_tounuse from t_1183_exchange_chance hh
where hh.f_exchange_type = guoqi_index.f_exchange_type
and hh.f_user_number = guoqi_index.f_user_number ; -------2,更新之前的过期卷数量加1-------------------
update t_1183_exchange_chance e
set e.f_exchange_tounuse = exchange_tounuse + 1
where e.f_exchange_type = guoqi_index.f_exchange_type
and e.f_user_number = guoqi_index.f_user_number ;
end;
end loop;
commit;
exception when others then
begin
rollback;
end;
end PROC_1183_EXCHANGE;
以上是存储过程
怎么写一个定时器让这个存储过程每天在指定的时间执行一次呢。。oracle定时器
1、创建JOB:
declare
jobno number;
begin
sys.dbms_job.submit(job => jobno,
what => 'p_test;',
next_date => to_date('01-08-2013 14:20:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+ 1/24');
commit;
end;
2、创建scheduler
begin
dbms_scheduler.create_job(
job_name => 'job_test',
enabled => true,
job_type => 'STORED_PROCEDURE',
job_action => 'p_test',
start_date => to_date('2013-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),
repeat_interval => 'FREQ=DAILY;INTERVAL=1');
end;