#1、查看所有job
select job,interval,what from user_jobs;
#2、新增job (按先后顺序分步骤执行)
--(1).创建删除一个月前操作日志的存储过程
create or replace procedure p_job_del_policy_status_log
as
begin
delete from bd_ad_policy_status_log t where t.create_time < sysdate -30;
commit;
end p_job_del_policy_status_log;
-- (2).定义变量
variable JOB number;
-- (3).设定Oraclejob 的执行开始时间以及周期
begin
sys.dbms_job.submit(job => :job,
what => 'P_SET_DERATE_ORDER_STAT;',
next_date => sysdate +interval '3' minute,
interval => 'TRUNC(sysdate+1)+1/24'); --每天1点执行一次
commit;
end;
/
-- (4).查询新创建JOB的ID
SELECT * FROM USER_JOBS;
-- (5).运行JOB
begin
dbms_job.run(JOB_ID); --这里的JOB_ID是第(4)步查到的ID
commit;
end;
/