Oracle 定时任务使用

1：首先创建存储过程

create or replace procedure pro_rqsl_hmd is
    rsCursor sys_refcursor;
    rqslid varchar2(100);
    nsrsbh varchar2(20);
    hmd_ksrq varchar2(10);
    hmd_jsrq varchar2(10);
    strSql   varchar2(1000);
begin
    strSql := ' select t.rqslid,t.nsrsbh,t.bzqx as hmd_ksrq,to_char(add_months(to_date(t.bzqx,''YYYY-MM-DD''),12),''YYYY-MM-DD'') as hmd_jsrq '||
           ' from rqsl_slxx t ' ||
           ' where t.rqslzt=1 and to_date(t.bzqx,''YYYY-MM-DD'')<sysdate ';
    
    open rsCursor for strsql;
    loop
      fetch rsCursor into rqslid,nsrsbh,hmd_ksrq,hmd_jsrq; 
      exit when rsCursor%NOTFOUND;
      insert into rqsl_hmd(nsrsbh,hmd_ksrq,hmd_jsrq) values(nsrsbh,hmd_ksrq,hmd_jsrq);
      update rqsl_slxx t set rqslzt=3 where t.rqslid=rqslid;
    end loop;
end pro_rqsl_hmd;

 

2：定义任务

JOB 每天凌晨执行一次。

对应脚本

begin
  sys.dbms_job.change(job => 3,
                      what => 'pro_rqsl_hmd;',
                      next_date => to_date('12-06-2018 14:36:49', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(sysdate+1)');
  commit;
end;
/