虚谷数据库-定时作业

虚谷数据库提供定时作业机制，用于定时、定期、自动的进行某些操作，可通过系统包 dbms_scheduler 进行定时作业创建、调度、查看、删除等。

DBMS_SCHEDULER 系统包封装了以下过程/函数：ENABLE、DISABLE、SET_JOB_ARGUMENT_VALUE、DROP_JOB、RUN_JOB。

过程/函数含义如下：

DBMS_SCHEDULER.ENABLE(job_name)：表示启用作业。 DBMS_SCHEDULER.DISABLE(job_name,TURE)：表示禁用作业。 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name,param_order,param_val)：表示对 job 调用存储过程进行参数赋值，param_order 为参数序，param_val 为参数值。 DBMS_SCHEDULER.RUN_JOB(job_name,TRUE)：表示显式调用定时作业。 DBMS_SCHEDULER.DROP_JOB(job_name,TRUE)：表示删除定时作业。

1、作业的创建

语法格式

DBMS_SCHEDULER.CREATE_JOB (
       job_name             IN VARCHAR2,
       job_type             IN VARCHAR2,
       job_action           IN VARCHAR2,
       number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
       start_date           IN TIMESTAMP                DEFAULT NULL,
       repeat_interval      IN VARCHAR2                 DEFAULT NULL,
       end_date             IN TIMESTAMP                 DEFAULT NULL,
       job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
       enabled              IN BOOLEAN                  DEFAULT FALSE,
       auto_drop            IN BOOLEAN                  DEFAULT TRUE,
       comments             IN VARCHAR2                 DEFAULT NULL);

参数解释：

• job_name：作业名称。
• job_type：作业类型，可指定为stored_procedure、plsql_block或plsql_command；若为stored_procedure则job_action内容为数据库存储过程名称；若为plsql_block则job_action为可执行块语句；若为plsql_command则job_action可调用包中的存储过程或存储函数。
• job_action：作业动作，与job_type相关。
• number_of_arguments：作业中存储过程、存储函数或包的参数个数。
• start_date：作业开始时间。
• repeat_interval：作业重复间隔说明。
• end_date：作业结束时间。
• job_class：作业类型——该参数暂时无效，预留。
• enabled：作业是否已激活，若该参数置为true则表示该作业默认为启用状态，作业根据其计划自动运行，若该参数置为false则该作业为禁用状态，不会自动执行，需手动执行作业或将该作业启用后方可自动执行。
• auto_drop：作业执行完是否自动删除。若该参数置为true则在作业完成后将自动删除作业，反之不会删除该作业。
• comments：作业备注说明。

参数说明：

• repeat_interval结构为REPEAT_INTERVAL=>’Freq=Minutely;Interval=5’ Freq关键字用于指定作业间隔的时间周期，可选参数包括：YEARLY（年）、MONTHLY（月）、WEEKLY（周）、DAILY（日）、HOURLY（小时）、MINUTELY（分）、SECONDLY（秒）。
• Interval关键字用于指定作业间隔频度，该值为一个整数，默认为1，可指定范围为1-999。
• repeat_interval参数除了以上两个限制条件外，还可指定：
  • BYHOUR：指示定时作业在指定小时执行，可指定范围 0-23，若要指定多个时间参数使用逗号进行分割，如BYHOUR=2,5,7。
  • BYDAY：指示在每周的第几天运行，可使用数字或英文缩写，如：MON|TUE|WED|THU|FRI|SAT|SUN等。
  • BYMONTHDAY：指示在每月的第几天运行。
  • BYMONTH：指示在每年的月份，可使用数字或英文缩写，如：JAN|FEB|MAR|APR|MAY|JUN等。

示例1

基于存储过程的定时作业。

CREATE TABLE JOB_TEST(ID INT,DT DATETIME);

CREATE OR REPLACE PROCEDURE JOB_PROC1(INPUT INTEGER) IS
BEGIN
  INSERT INTO JOB_TEST VALUES (INPUT, SYSDATE);
END;

EXEC dbms_scheduler.create_job(
  'job1',
  'stored_procedure',
  'JOB_PROC1',
  1,
  sysdate,
  'freq=secondly;INTERVAL=30;byhour=5,14;',
  '2029-01-01 01:00:00',
  'default_class',
  FALSE,
  TRUE,
  '这是一个测试');

示例2
基于PLSQL块语句的定时作业

EXEC dbms_scheduler.create_job('job2',
  'plsql_block',
  'DECLARE BEGIN FOR i IN 1..10 LOOP INSERT INTO JOB_TEST VALUES(i,sysdate);END LOOP;END;',
  0,
  sysdate,
  'freq=secondly;INTERVAL=30;byhour=5,14;',
  '2029-01-01 01:00:00',
  'default_class',
  FALSE,
  TRUE,
  '这是一个测试');

2、过程/函数的使用

（1）DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE该过程用于设置作业的参数信息。可通过位置绑定参数也可以通过参数名称绑定参数。

语法格式

-- argument_value支持VARCHAR2、TINYINT、SMALLINT、INTEGER、BIGINT、FLOAT、DOUBLE、NUMERIC、BOOLEAN、TIME、DATE、DATETIME数据类型，此处语法仅列出VARCHAR2类型作为参考。

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name               IN VARCHAR2,
   argument_position      IN PLS_INTEGER,
   argument_value         IN VARCHAR2);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name               IN VARCHAR2,
   argument_name          IN VARCHAR2,
   argument_value         IN VARCHAR2);

参数解释：

• job_name：需要设置参数的作业名称。
• argument_position：需要设置参数值的参数位置。
• argument_name：需要设置参数值的参数名称。
• argument_value：设置的参数值，该处根据参数值类型自动选择重载的存储过程。

示例：

SET_JOB_ARGUMENT_VALUE
BEGIN
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name        => 'job1',
    argument_name   => 'INPUT',
    argument_value  => 100
  );
END;
/

上面这个示例是使用 ​DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE​ 过程来更新名为 ​job1​ 的作业的参数 ​INPUT​ 的值为 ​100​。

（2）SET_ATTRIBUTE

​DBMS_SCHEDULER.SET_ATTRIBUTE​ 该存储过程用于修改定时作业属性值，而不是参数。如果你想修改作业的某些属性，例如作业的名称、作业的开始时间、作业的结束时间等，你可以使用​SET_ATTRIBUTE​。

语法格式如下：

DBMS_SCHEDULER.SET_ATTRIBUTE (
     name           IN VARCHAR2,
     attribute      IN VARCHAR2,
     value          IN {VARCHAR2 | TIMESTAMP | PLS_INTEGER | BOOLEAN});

参数解释：

• name：需要修改属性的作业名称。
• attribute：需要修改的属性名称。
• value：修改的属性对应的新值，该参数可接受VARCHAR2、TIMESTAMP、PLS_INTEGER、BOOLEAN数据类型的值。

示例：

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'job1',
    attribute => 'repeat_interval',
    value => 'freq=secondly;INTERVAL=15;byhour=5,14;'
  );
END;
/

在这个例子中，展示了如何使用 ​SET_ATTRIBUTE​ 来修改作业的间隔时间，我们将 ​job1​ 作业的重复间隔从原来的 ​30​ 秒修改为 ​15​ 秒，并且保持 ​byhour​ 属性为 ​5,14​，这意味着作业将在每天的 ​5​ 点和 ​14​ 点执行。

（3）RUN_JOB

RUN_JOB该存储过程用于显式调用作业

语法格式如下：

DBMS_SCHEDULER.RUN_JOB (
   job_name                IN VARCHAR2,
   use_current_session     IN BOOLEAN DEFAULT TRUE);

参数解释：

• job_name：调用的作业名称。
• use_current_session：参数选用true时表示使用当前会话执行该作业，若作业未执行完成则当前连接会话一直处于阻塞状态直至作业完成后方可执行其他SQL；若参数选用false则表示使用其他会话执行该作业，当前会话可继续执行其他SQL。

示例：

BEGIN
  DBMS_SCHEDULER.RUN_JOB(job_name => 'job1');
END;
/

DBMS_SCHEDULER.RUN_JOB​ 函数被调用来立即运行名为 ​job1​ 的作业。这个函数会立即触发作业的执行，即使作业的计划时间还没有到达。

（4）ENABLE、DISABLE

ENABLE存储过程用于启用作业，将作业是否启用属性设置为true，然后作业根据其开始时间、结束时间和间隔时间自动运行作业。
DISABLE存储过程用于禁用作业同ENABLE作用相反。

语法格式如下：

DBMS_SCHEDULER.ENABLE (
    name                  IN VARCHAR2);

DBMS_SCHEDULER.DISABLE (
    name                  IN VARCHAR2,
    force                 IN BOOLEAN DEFAULT FALSE);

参数解释：

• name：禁用或启用的作业名称。
• force：如果设置为true则需先停止正在运行的作业实例，再禁用作业；如果设置为false则允许正在运行的作业执行完成，然后再禁用作业。

示例：

--enable启用作业
BEGIN
  DBMS_SCHEDULER.ENABLE(name => 'job1');
END;
/

--disable禁用作业
BEGIN
  DBMS_SCHEDULER.DISABLE(name => 'job1');
END;
/

在这两个例子中，​name => ‘job1’​ 参数指定了要启用或禁用的作业的名称。执行 ​DBMS_SCHEDULER.ENABLE​ 后，作业将开始按照其计划运行。执行 ​DBMS_SCHEDULER.DISABLE​ 后，作业将停止运行，直到再次被启用

（5）DROP_JOB

DROP_JOB该存储过程用于删除作业。

语法格式如下：

DBMS_SCHEDULER.DROP_JOB (
   job_name               IN VARCHAR2,
   force                  IN BOOLEAN DEFAULT FALSE);

参数解释：

• job_name：删除的作业名称。
• force：如果设置为true则需先停止正在运行的作业实例，再删除作业；如果设置为false则允许正在运行的作业执行完成，然后再删除这些作业。

示例：

BEGIN
  DBMS_SCHEDULER.DROP_JOB(job_name => 'job1');
END;
/

在这个例子中，​job_name => ‘job1’​ 参数指定了要删除的作业的名称。执行 ​DBMS_SCHEDULER.DROP_JOB​ 后，名为 ​job1​ 的作业将从数据库中被永久删除。