oracle定时任务

2021-11-14 02:53:51

一、简介
　　当我们需要oracle数据库定时自动执行一些脚本，或进行数据库备份、数据库的性能优化，包括重建索引等工作是需要使用到定时任务。
　　定时任务可以使用以下两种完成。
　　1、操作系统级的定时任务，win的定时任务，unix的crontab
　　2、数据库级的定时任务。
　　相对来说，数据库的定时任务效率会更高。
　　oracle的定时任务：使用oracle定时任务时，需要使用到oracle中dbms_job.submit函数。

DBMS_JOB过程的公共参数

Job BINARY_INTEGER 任务的唯一识别号
What VARCHAR2 作为任务执行的PL/SQL代码
Next_date VARCHAR2 任务下一次运行的时间
Interval VARCHAR2 日期表达式，用来计算下一次任务运行的时间

1、job参数
　　job是一个整数，用来唯一标示一个任务。该参数既可由用户指定也可由系统自动赋值，这取决于提交任务时选用了哪一个任务提交过程。DBMS_JOB.SUBMIT过程通过获得序列SYS.JOBSEQ的下一个值来自动赋予一个任务号。该任务号是作为一个OUT参数返回的，所以调用者可以识别出提交的任务。而DBMS_JOB.ISUBMIT过程则由调用者给任务指定一个识别号，此时，任务号的唯一性就完全取决于调用者。除了删除或者重新提交任务，一般来说任务号是不能改变的。即使当数据库被导出或者被导入这样的情况，任务号也将被保留下来。所以在执行含有任务的数据的导入/导出操作时很可能会发生任务号冲突的现象。

2、what
　　what参数是一个可以转化为合法PL/SQL调用的字符串，该调用将被任务队列自动执行。在what参数中，如果使用文字字符串，则该字符串必须用单引号引起来。 what参数也可以使用包含我们所需要字符串值的VARCHAR2变量。实际的PL/SQL调用必须用分号隔开。在PL/SQL调用中如果要嵌入文字字符串，则必须使用两个单引号。 what参数的长度在Oracle7.3中限制在2000个字节以内，在Oracle 8.0以后，扩大到了4000个字节，这对于一般的应用已完全足够。该参数的值一般情况下都是对一个PL/SQL存储过程的调用。实际经验就是最好将存储过程调用封装在一个匿名块中，这样可以避免一些错误的产生。如，一般情况下，what参数可以这样引用： what =>’my_procedure(parameter1);’ 但是比较安全的引用，应该这样写：what =>’begin my_procedure(parameter1); end;’。任何时候，我们只要通过更改what参数就可以达到更改任务定义的目的。但是有一点需要注意，通过改变what参数来改变任务定义时，任务的运行时间将还是和之前的一样。需要重新设置新的运行时间，从而达到定时任务的目的。

3、next_date
　　next_date参数是用来调度任务队列中该任务下一次运行的时间。这个参数对于DBMS_JOB.SUBMIT和DBMS_JOB.BROKEN这两个过程默认为系统当前时间，也就是说任务将立即运行。当将一个任务的next_date参数赋值为null时，则该任务下一次运行的时间将被指定为4000年1月1日，也就是说该任务将永远不再运行。但是，如果想在任务队列中保留该任务而又不想让其运行，可以将next_date设置为null。Next_date也可以设置为过去的一个时间。系统任务的执行顺序是根据它们下一次的执行时间来确定的，所以将next_date参数设置回去就可以达到将该任务排在任务队列前面的目的。这在当任务队列进程不能跟上将要执行的任务或者一个特定的任务需要尽快执行的时候可以这样设置。

4、Interval
　　interval 参数是一个表示Oracle合法日期表达式的字符串。这个日期字符串的值在每次任务被执行时算出，算出的日期表达式有两种可能，要么是未来的一个时间要么就是null。next_date是在一个任务开始时算出的，而不是在任务成功完成时算出的。当任务成功完成时，系统通过更新任务队列目录表将前面算出的next_date值置为下一次任务要运行的时间。当由interval表达式算出next_date是null时，任务自动从任务队列中移出，不会再继续执行。因此，如果传递一个null值给interval参数，则该任务仅仅执行一次。通过给interval参数赋各种不同的值，可以设计出复杂运行时间计划的任务。

二、例子

　　存储过程更多情况是在数据库方作数据整合等复杂的工作.比如银行系统，数据相当重要，每天需要备份重要表的数据。每天的数据有十多万，此时需要用到定时任务进行备份。

一般思路如下：
1.每天备份完数据之后，max(id)存到一张表
2.下次备份的时候select max(id);
3.select * from table where id > max(id);

　　示例代码：

每分钟备份一次person表中增加的记录

person表结构如下：

CREATE TABLE person(
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

备份表person_back表结构如下：

CREATE TABLE person_back (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

辅助表tb_maxid，用于存放当前person表中最大的id

create table tb_maxid(id number);
insert into tb_maxid values(0);

其他代码如下：

/*
  1、提取person，所有数据
  2、循环插入到person_back表中
  3、完成之后记录max(id)到tb_maxid表
  
  主要问题：
  1、如果在循环中commit，效率会很低
  2、假设数据很大（千万）回滚段不够(抛异常)。回滚段的默认大小可以设置
       2.1、加大回滚段。
       2.2分段提交
*/
-- 存储过程，保存person记录
create or replace procedure pro_back_person(
       v_maxid number
)
is
   -- 定义游标
   cursor c_person is
   select * from person where id > v_maxid;
   --定义rowtype
   r_person person%rowtype;
   --判断循环次数
   v_index number := 0;
   -- 
   v_max number;
begin
  -- 判断游标是否打开
  if c_person%isopen then --游标打开
    null;
  else
   open c_person;
  end if;
  --循环备份数据
  loop
    fetch c_person into r_person;
    exit when c_person%notfound;
    
    insert into person_back values(r_person.id,r_person.username, r_person.age, r_person.password);
  
    --分段提交
    v_index := v_index + 1;   
    if(v_index = 2000) then
       commit;
       v_index := 0;
    end if;
    
  end loop;
  commit; --再次提交数据
  
  select max(id) into v_max from person_back;
  update tb_maxid set id = v_max;
  commit;
  --关闭游标
  close c_person;
end pro_back_person;
-- 调用保存记录的存储过程
create or replace procedure invokebackpro
is
   v_maxid number;
begin
  select id into v_maxid from tb_maxid;
  pro_back_person(v_maxid);
end invokebackpro;
-- 创建任务定时器
declare
    jobno number;
begin
  dbms_job.submit(
    jobno,
    what => 'invokebackpro;', --invokebackpro为存储过程的名称
    Interval => 'TRUNC(sysdate, ''mi'')+1/(24*60)' --定义事件间隔每分钟
  );
  commit;
end;

三、其他相关

一些常见Interval设置如下：

1、每分钟执行
    Interval => TRUNC(sysdate, 'mi') + 1/(24*60)
2、每天定时执行(每天的凌晨2点执行)
    Interval => TRUNC(sysdate) + 1 + 2/(24)
3、每周定时执行(每周一凌晨2点执行)
    Interval => TRUNC(next_day(sysdate, 2)) + 2/(24)
4、每月定时执行(每月1日凌晨2点执行)    
    Interval => TRUNC(ADD_MONTHS(sysdate)) + 1 + 2/24
5、没季度定时执行(没嫉妒的定义天凌晨2点执行)
    Interval => TRUNC(ADD_MONTHS(sysdate, 3), 'Q') + 2/24
6、每半年定时执行(每年7月1日和1月1日凌晨2点)
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 6) + 2/24
7、每年定时执行(每年1月1日凌晨2点执行)    
    Interval => TRUNC(ADD_MONTHS(sysdate, 'yyyy'), 12) + 2/24

　　oracle中关于定时器的数据字典如下：
　　DBA_JOBS 本数据库中定义到任务队列中的任务
　　DBA_JOBS_RUNNING 目前正在运行的任务
　　USER_JOBS 当前用户拥有的任务

码农公寓

相关文章