定时作业
📄字数 6.5K
👁️阅读量 加载中...
定时作业是一种在数据库内部定义的自动化操作，在预定的时间或间隔执行特定的数据库操作，以简化管理任务、提高数据处理效率。定时作业通过数据库管理系统（DBMS）内置系统包及其函数来实现，可以减少人为干预，降低错误风险，提高系统的稳定性。
一、创建定时作业
1.1 语法格式
sql
SQL> EXEC DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR,
job_type IN VARCHAR,
job_action IN VARCHAR,
number_of_arguments IN INTEGER DEFAULT 0,
start_date IN TIMESTAMP DEFAULT NULL,
repeat_interval IN VARCHAR DEFAULT NULL,
end_date IN TIMESTAMP DEFAULT NULL,
job_class IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR DEFAULT NULL);1.2 参数说明
job_name：定时作业名称，名称必须满足数据库对象命名规则中列出的要求。job_type：定时作业类型，可指定为stored_procedure、plsql_block或plsql_command。由于plsql_block和plsql_command不能指定number_of_arguments（参数个数），所以它们不能执行有参数的存储过程、函数和包。- 若为
stored_procedure则job_action内容为数据库存储过程名称。 - 若为
plsql_block则job_action为可执行块语句，dml语句和ddl语句可以封装为块语句执行。 - 若为
plsql_command则job_action可调用包中的存储过程或存储函数，可执行正常的sql语句，例如dml和ddl。
- 若为
job_action：定时作业动作，与job_type相关，根据不同的job_type可以选择执行不同的动作，详见job_type中的描述。number_of_arguments：定时作业中存储过程、存储函数或包的参数个数。start_date：定时作业开始时间，需小于定时作业结束时间，否则会报错。repeat_interval：定时作业重复间隔说明。end_date：定时作业结束时间，需大于定时作业开始时间并且大于等于系统当前时间，否则报错，如果给NULL则不会结束一直调度运行，NULL在此处代表时间类型最大值。job_class：定时作业类型，为预留参数。enabled：定时作业是否已激活，若该参数置为TRUE则表示该定时作业默认为启用状态，定时作业根据其计划自动运行，若该参数置为FALSE则该定时作业为禁用状态，不会自动执行，需手动执行定时作业或将该定时作业启用后方可自动执行。auto_drop：定时作业执行完是否自动删除。若该参数置为TRUE则在定时作业完成后将自动删除定时作业，反之不会自动删除该定时作业。comments：定时作业备注说明。
1.2.1 repeat_interval说明
repeat_interval语法结构：
sql
REPEAT_INTERVAL => 'FREQ=<time_period>; INTERVAL=<integer>;
[BYHOUR=<hour_list>
| BYDAY=<day_list>
| BYMONTHDAY=<day_list>
| BYMONTH=<month_list>
| BYWEEKNO=<week_list>
| BYYEARDAY=<day_list>
| BYDATE=<date_list>
| BYMINUTE=<minute_list>
| BYSECOND=<second_list>]'FREQ关键字用于指定定时作业间隔的时间周期，必选项，<time_period>可选参数包括：YEARLY（年）、MONTHLY（月）、WEEKLY（周）、DAILY（日）、HOURLY（小时）、MINUTELY（分）、SECONDLY（秒）、ONCE（执行一次）。INTERVAL关键字用于指定定时作业间隔频度，该值为一个整数，可选项，默认为1，可指定范围为1-999。
- by项目，可选项，具体如下：
BYHOUR：指示定时作业在指定小时执行，可指定范围 0-23，若要指定多个时间参数使用逗号进行分割，如BYHOUR=2,5,7。BYDAY：指示在每周的第几天运行，可使用英文全称或英文缩写，如：MON|TUE|WED|THU|FRI|SAT|SUN等。BYMONTHDAY：指示在每月的第几天运行。BYMONTH：指示在每年的月份，可使用数字、英文全称或英文缩写，如：JAN|FEB|MAR|APR|MAY|JUN等。BYWEEKNO：指定在一年的哪一周运行，范围1-53。BYYEARDAY：指定在一年的哪一天运行，范围1-366。BYDATE：指定一年的哪一天运行，范围1-366。BYMINUTE：指定在小时内的哪一分钟运行，范围0-59。BYSECOND：指定在分钟内的哪一秒运行，范围0-59。
1.3 常见问题和注意事项
- 定时作业开始时间和结束时间不能相同。
- 定时作业在RUNNING状态时，删除该定时作业不会立即删除掉，而是会等定时作业执行结束后再删除。
- 定时作业的类型决定了当前定时作业可以执行的操作，但目前带参数的包不能被定时作业执行。
- 定时作业权限请参阅权限管理。
- repeat_interval中，有几种时间格式是互斥的，具体情况如下表。
| Freq间隔周期）\ BY项目（指定时间） | BYMONTH | BYWEEKNO | BYYEARDAY | BYDATE | BYMONTHDAY | BYDAY | BYHOUR | BYMINUTE | BYSECOND |
|---|---|---|---|---|---|---|---|---|---|
| FREQ_YEARLY | √ | √ | √ | √ | √ | √ | √ | √ | √ |
| FREQ_MONTHLY | × | √ | × | × | √ | √ | √ | √ | √ |
| FREQ_WEEKLY | × | × | × | × | × | √ | √ | √ | √ |
| FREQ_DAILY | × | × | × | × | × | × | √ | √ | √ |
| FREQ_HOURLY | × | × | × | × | × | × | √ | √ | √ |
| FREQ_MINUTELY | × | × | × | × | × | × | √ | √ | √ |
| FREQ_SECONDLY | × | × | × | × | × | × | √ | √ | √ |
1.4 示例
- 基于存储过程的定时作业
创建一个基于存储过程的定时作业，每天的5点与14点时，每隔30秒运行一次。
sql
SQL> CREATE TABLE JOB_TEST(ID INT,DT DATETIME);
SQL> CREATE OR REPLACE PROCEDURE JOB_PROC1(INPUT INTEGER) IS
BEGIN
INSERT INTO JOB_TEST VALUES (INPUT, SYSDATE);
END;
/
SQL> 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,
'这是一个测试');
-- 查询定时作业信息
SQL> SELECT job_name,job_type,job_param,job_action,begin_t,end_t,repet_interval,trig_events,last_run_t,state FROM SYS_JOBS WHERE JOB_NAME = 'job1' ;
+----------+------------------+-----------+------------+--------------------------+--------------------------+----------------------------------------+-------------+--------------------------+-------+
| JOB_NAME | JOB_TYPE | JOB_PARAM | JOB_ACTION | BEGIN_T | END_T | REPET_INTERVAL | TRIG_EVENTS | LAST_RUN_T | STATE |
+----------+------------------+-----------+------------+--------------------------+--------------------------+----------------------------------------+-------------+--------------------------+-------+
| job1 | stored_procedure | <NULL> | <CLOB>| 2025-07-23 14:31:54.147 | 2029-01-01 01:00:00.000 | freq=secondly;INTERVAL=30;byhour=5,14; | <NULL> | 1970-01-01 00:00:00.000 | IDLE |
+----------+------------------+-----------+------------+--------------------------+--------------------------+----------------------------------------+-------------+--------------------------+-------+- 基于PLSQL块语句的定时作业
创建一个基于PLSQL块语句的定时作业，该定时作业在每天的5点与14点时，每隔30秒运行一次。
sql
SQL> 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,
'这是一个测试');- 基于包语句的定时作业
sql
SQL> CREATE TABLE job_tb3(id INT,name VARCHAR,dt DATETIME);
SQL> CREATE OR REPLACE PACKAGE job_pack IS
PROCEDURE ins;
END;
/
SQL> CREATE OR REPLACE PACKAGE BODY job_pack IS
PROCEDURE ins IS
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO job_tb3 VALUES(i*2,'job'||i,ADD_MONTHS(sysdate,i));
END LOOP;
END;
END;
/
SQL> CREATE OR REPLACE PROCEDURE pro_cre_job4(dt DATETIME,dt2 DATETIME) IS
BEGIN
DBMS_SCHEDULER.CREATE_JOB('123',
'plsql_command',
'JOB_PACK.INS',
0,
dt,
'FREQ=DAILY;INTERVAL=1;',
dt2,
'DEFAULT_JOB_CLASS',
TRUE,
TRUE,
'123');
END;
/二、设置定时作业参数
该方法用于设置定时作业的参数信息。可通过位置绑定参数也可以通过参数名称绑定参数。
2.1 语法格式
sql
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR,
argument_position IN INTEGER,
argument_value IN VARCHAR);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR,
argument_name IN VARCHAR,
argument_value IN VARCHAR);2.2 参数说明
job_name：需要设置参数的定时作业名称。argument_position：需要设置参数值的参数位置。argument_name：需要设置参数值的参数名称。argument_value：设置的参数值，该处根据参数值类型自动选择重载的存储过程，支持 VARCHAR、TINYINT、SMALLINT、INTEGER、BIGINT、FLOAT、DOUBLE、NUMERIC、BOOLEAN、TIME、DATE、DATETIME 数据类型，上述语法仅列出VARCHAR类型作为参考，类型隐式转换不支持。
注意
参数位置必须与存储过程定义的参数顺序一致。
2.3 示例
sql
SQL> CREATE TABLE job_tb5(id INT,name VARCHAR,dt DATETIME);
SQL> CREATE OR REPLACE PROCEDURE job_pro2(dt DATETIME) IS
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO job_tb5 VALUES(i,'job'||i,ADD_MONTHS(dt,i));
END LOOP;
END;
/
SQL> EXEC DBMS_SCHEDULER.CREATE_JOB('job_doc',
'stored_procedure',
'JOB_PRO2',
1,
sysdate,
'FREQ=secondly;INTERVAL=1;',
sysdate + 1/24/60,
'DEFAULT_JOB_CLASS',
FALSE,
TRUE,
'job_doc');
SQL> EXEC DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('job_doc',1,sysdate); --传入参数三、修改定时作业属性
该方法用于修改定时作业属性值。
3.1 语法格式
sql
DBMS_SCHEDULER.SET_ATTRIBUTE (
name IN VARCHAR,
attribute IN VARCHAR,
value IN {VARCHAR | TIMESTAMP | INTEGER | BOOLEAN});3.2 参数说明
name：需要修改属性的定时作业名称。attribute：需要修改的属性名称，支持修改start_date，end_date，repet_interval，job_action，number_of_arguments，auto_drop，comments。value：修改的属性对应的新值，该参数可接受VARCHAR、TIMESTAMP、INTEGER、BOOLEAN数据类型的值。
3.3 常见问题和注意事项
- 定时作业
JOB_TYPE在定时作业创建完成后不允许修改。 - 修改定时作业的参数时需关注参数是否合理。
- 修改参数的类型须符合参数本身类型。
3.4 示例
sql
SQL> EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('job_doc','comments','备份'); --修改定时作业的备注信息四、执行定时作业
该方法用于手动执行定时作业。
4.1 语法格式
sql
DBMS_SCHEDULER.RUN_JOB (
job_name IN VARCHAR,
use_current_session IN BOOLEAN DEFAULT TRUE);4.2 参数说明
job_name：调用的定时作业名称。use_current_session：参数选用TRUE时表示使用当前会话执行该定时作业，若定时作业未执行完成则当前连接会话一直处于阻塞状态直至定时作业完成后方可执行其他SQL；若参数选用FALSE则表示使用其他会话执行该定时作业，当前会话可继续执行其他SQL。
4.3 常见问题和注意事项
- 手动执行job时，该job只会执行一次，且此次运行不受
REPEAT_INTERVAL参数影响。。
4.4 示例
sql
SQL> DBMS_SCHEDULER.RUN_JOB('job_doc',FALSE); --执行job五、启用/禁用定时作业
ENABLE用于启用定时作业，将定时作业是否启用属性设置为TRUE，然后定时作业根据其开始时间、结束时间和间隔时间自动运行定时作业。
DISABLE用于禁用定时作业同ENABLE作用相反。
5.1 语法格式
sql
DBMS_SCHEDULER.ENABLE (
name IN VARCHAR);
DBMS_SCHEDULER.DISABLE (
name IN VARCHAR,
force IN BOOLEAN DEFAULT FALSE);5.2 参数说明
name：禁用或启用的定时作业名称。force：如果设置为TRUE则需先停止正在运行的定时作业实例，再禁用定时作业；如果设置为FALSE则允许正在运行的定时作业执行完成，然后再禁用定时作业。
5.3 示例
sql
--启用定时作业test
SQL> EXEC DBMS_SCHEDULER.ENABLE('job_doc');
SQL> EXEC DBMS_SCHEDULER.DISABLE('job_doc');六、删除定时作业
该方法用于删除定时作业。
6.1 语法格式
sql
DBMS_SCHEDULER.DROP_JOB (
job_name IN VARCHAR,
force IN BOOLEAN DEFAULT FALSE);6.2 参数说明
job_name：删除的定时作业名称。force：如果设置为TRUE则需先停止正在运行的定时作业实例，再删除定时作业；如果设置为FALSE则允许正在运行的定时作业执行完成，然后再删除这些定时作业。
6.3 常见问题和与注意事项
- 删除定时作业并不能立即执行，RUNNING状态的作业需等待执行完成才会删除。
- 定时作业属性
enable为TRUE时，间隔时间设置太长，但是在间隔期间删除定时作业，有可能会等到下一次调度执行才会删除该定时作业。
6.4 示例
sql
SQL> EXEC DBMS_SCHEDULER.DROP_JOB('job_doc',FALSE);七、JOB_NEXT_RUNTIME
JOB_NEXT_RUNTIME获取由参数JOB_NAME指定定时作业的下次运行时间。
7.1 方法声明
sql
FUNCTION JOB_NEXT_RUNTIME(
JOB_NAME IN VARCHAR,
LAST_RT IN DATETIME) RETURN DATETIME;7.2 参数说明
- JOB_NAME：定时作业名称。
- LAST_RT：上次运行时间，可通过系统表查询。
7.3 操作示例
sql
SQL> SELECT DBMS_SCHEDULER.JOB_NEXT_RUNTIME('job_doc','1970-01-01 00:00:00');
+--------------------------+
| EXPR1 |
+--------------------------+
| 2025-06-18 13:42:13.000 |
+--------------------------+八、NEXT_RUNTIME
该方法用于获取按调度字串计算得到的下次运行时间，NEXT_RUNTIME与JOB_NEXT_RUNTIME的区别在于前者是按照REPEAT_INTERVAL参数指定的时间格式串来计算下次运行的时间，后者是按照JOB_NAME参数指定的job中的时间格式串来运算下一次运行的时间。
8.1 方法声明
sql
FUNCTION NEXT_RUNTIME(
REPEAT_INTERVAL IN VARCHAR,
LAST_RT IN DATETIME) RETURN DATETIME;8.2 参数说明
- REPEAT_INTERVAL：定时作业执行周期。
- LAST_RT：上次运行时间，可通过系统表查询。
8.3 操作示例
sql
SQL> SELECT DBMS_SCHEDULER.NEXT_RUNTIME('FREQ=HOURLY;INTERVAL=1;','1970-01-01 00:00:00');
+--------------------------+
| EXPR1 |
+--------------------------+
| 2025-06-18 14:00:00.000 |
+--------------------------+