Oracle——24定时任务

            我们有时候会这么一个需求：希望数据库能够定时的帮我们做某一件事。比如说我们希望数据库能够定期的把库里面某一张表、或某些表的数据汇总到另外一张汇总表里面。那么这个时候我们就可以通过使用数据库的定期任务来实现这么一个功能。

            下面我们来举一个简单的例子，假如我们有如下两张表，t1和t2。

t1及其对应的sequence:

create table t1(a1 integer primary key, a2 varchar2(20), a3 varchar2(20), a4 date);

create sequence seq_t1 start with 1 increment by 1;

 

 

t2及其对应的sequence：

create table t2(a1 integer primary key, a2 varchar2(20), a3 date);

create sequence seq_t2 start with 1 increment by 1;

            我们需要在每天凌晨两点的时候把表t1里面的a2和a4作为表t2里面的a2和a3插到表t2。Oracle的定期任务只是定时的执行某一个任务，但具体是什么任务还需要我们自己来指定。所以首先我们得来建立自己的任务，任务通常都是一个存储过程。这里我们建立如下存储过程：

 

create or replace procedure copy_t1_to_t2 as
  startTime date := trunc(sysdate - 1); --昨天0点
  endTime   date := trunc(sysdate); --今天0点
  cursor t1_cursor is
    select a2, a4
      from t1
     where a4 >= startTime
       and a4 < endTime; --定义一个游标查询t1昨天的记录
begin
  for t in t1_cursor loop
    insert into t2 (a1, a2, a3) values (seq_t2.nextval, t.a2, t.a4);--把t1里面的相关数据插到t2中
    commit;--插入一条记录commit一次
  end loop;
end;

 

            

 

          建立定时任务

 

            建立好任务以后，我们就可以把它提交给Oracle的定时任务队列里面了，这里我们建立代码如下：

SQL> var job number;

SQL> begin

  2    dbms_job.submit(

  3          job => :job,

  4          what => 'copy_t1_to_t2;',

  5          next_date => to_date('2013-11-20 02:00:00','yyyy-MM-dd hh24:mi:ss'),

  6          interval => 'trunc(sysdate+1)+2/24'

  7    );

  8    commit;

  9  end;

 10  /

            其实上面就是在执行dbms_job包的submit这么一存储过程，在上面的代码里面我们一共指定了四个参数：job、what、next_date和interval。下面我们来介绍一下这四个参数的含义：

            job：是一个输出参数，代表任务序号，所以我们得先定义一个job变量。

            what：输入参数，表示需要做什么事，记得别忘记了存储过程后面的那个分号“；”。

            next_date：输入参数，日期类型，表示下次执行该任务的时间，在这里也就是第一次执行该任务的时间。

            interval：输入参数，字符类型，从字面上看是表示任务之间的时间间隔，实际上它还是表示下次的执行时间，只不过它是不断变化的，所以通常我们都会写sysdate+某一个时间，如：

            sysdate+1/(24*60)：表示隔1分钟执行一次；

            sysdate+5/(24*60)：表示隔5分钟执行一次；

            sysdate+1:       表示隔1天执行一次；

            trunc(sysdate+1)+2/24：表示每天早上2点执行一次；

 

            在上面代码中，我们把任务提交到Oracle的定期任务队列里面之后马上就commit了一下，这样我们的定期任务就会处于执行状态了。之后我们可以从user_jobs视图里面查看定时任务的运行情况了。

 

          运行定时任务

            如果我们在创建的时候没有进行commit操作，我们的定时任务是没有执行的。这个时候就需要我们手动的来触发定时任务的执行了。操作代码是：

begin
  dbms_job.run(jobNum);
end;
/

 

            jobNum就是我们之前创建定时任务时返回的那个job的值。执行之后就表示我们需要运行哪个定时任务。

          删除定时任务

            删除定时任务的操作是：

 

 

 

 

begin
  dbms_job.remove(jobNum);
end;
/

 

            

表示需要删除哪个定时任务。