在实际业务中,有时会遇到定时执行某个动作或收集某项数据的情况,这时就需要使用数据库的定时任务来完成。
DM数据库支持使用DM作业系统、DBMS_JOB或DBMS_SCHEDULER系统包来创建定时任务。其中DBMS_JOB、DBMS_SCHEDULER是DM兼容Oracle定时任务功能的系统包,并且DM提供了兼容Oracle定时任务相关视图的功能。
本章介绍使用DBMS_JOB和DBMS_SCHEDULER创建定时任务的方法。
方式一
使用DBMS_JOB创建作业
DBMS_JOB是DM兼容Oracle定时任务功能的系统包,按指定的时间或间隔执行用户定义的作业。并兼容DBA_JOBS、USER_JOBS、DBA_JOBS_RUNNING等Oracle作业相关的数据字典视图。
创建DBMS_JOB系统包
DM数据库安装完成之后默认不创建DBMS_JOB系统包,如果要使用DBMS_JOB系统包需要手工创建,创建或删除DBMS_JOB系统包语法如下:
void SP_INIT_JOB_SYS(
CREATE_FLAG INT
)
参数详解 CREATE_FLAG为1时表示创建DBMS_JOB包;为0表示删除该系统包。
DBMS_JOB相关方法
DBMS_JOB创建JOB有两种过程,ISUBMIT和SUBMIT,两者的区别是ISUBMIT的JOB编码是IN参数,由编码员指定,SUBMIT的JOB编码是OUT参数,由系统自动生成。DBMS_JOB提供创建、修改、删除、禁用JOB等功能,详细功能请参考《DM8系统包使用手册》。
ISUBMIT过程
ISUBMIT使用特定的JOB编码提交一个作业。这个过程与SUBMIT过程相似,唯一区别是JOB参数作为IN型参数传递(SUBMIT过程JOB参数是OUT类型,由系统自动生产JOB编号)。如果提供的工作号已被使用,将产生一个错误。语法如下:
PROCEDUREISUBMIT(
JOBIN INT,
WHATIN VARCHAR2(1800),
NEXT_DATEIN DATETIME,
"INTERVAL"IN VARCHAR(100),
NO_PARSEIN BOOLEAN DEFAULTFALSE
);
参数详解
JOB用户指定的工作编号,不能为负数,且必须是不存在的工作编号,唯一。
WHAT表示将被执行的代码,可以是DMSQL代码块和存储过程。
NEXT_DATEJOB下次运行时间,即何时将运行这个工作。
"INTERVAL"作业间隔,即何时这个工作将被重执行。
NO_PARSE表示此工作在提交时或执行时是否进行语法分析,TRUE指示此PL/SQL代码在它第一次执行时应进行语法分析,而FALSE
指示本PL/SQL代码应立即进行语法分析。
使用方法
假设我们需要创建一个JOB,第二天凌晨开始运行,每5分钟运行一次,运行时调用存储过程p_test_job往t_test表中插入数据。实现步骤参考如下:
1.创建DBMS_JOB系统包
调用SP_INIT_JOB_SYS创建DBMS_JOB系统包:
SP_INIT_JOB_SYS(1);
创建完成之后,数据库模式下面多了SYSJOB模式,该模式属于SYSDBA用户,SYSJOB模式拥有DBMS_JOB系统包、相关表和视图。如下图所示。
2.赋予用户相关权限
普通用户创建JOB需要admin job权限,调用DBMS_JOB系统包需要有执行该包的权限,使用管理员SYSDBA用户赋予DMHR用户该权限。语句参考如下:
grant admin job to dmhr;
grant execute on dbms_job to dmhr;
3.创建作业
①创建T_TEST表:
create table t_test(
id int identity(1,1),
inserttime datetime,
jobtypevarchar2(30)
);
②创建存储过程P_TEST_JOB往该表插入数据:
create or replace procedure p_test_job
as
begin
insert into t_test(inserttime,jobtype)
values (sysdate,'DBMS_JOB');
commit;
end;
③创建JOB号编码为1001的定时任务,第二天凌晨开始运行,每5分钟运行一次:
begin
dbms_job.isubmit(
job => 1001,
what => 'p_test_job;',
next_date => trunc(sysdate)+1,
"INTERVAL" => 'trunc(sysdate)+1+5/(24*60)');
commit;
end;
语句执行完成,查看USER_JOBS视图可以看到上述步骤创建的JOB。如果JOB正在执行,可以在DBA_JOBS_RUNNING中看到该JOB运行数据。
方式二:使用DBMS_SCHEDULER创建作业
DBMS
_SCHEDULER包也是兼容Oracle产品主要作业功能的系统包。DBMS_SCHEDULER包提供一系列调度相关的存储过程及方法供DMSQL调用,目前主要兼容ORACLE的DBMS_SCHEDULER包中常用的方法和数据字典视图。
与DBMS_JOB相比,DBMS_SCHEDULER包的功能要强大很多,但使用起来相对复杂。很学初学者容易被它的复杂性吓跑,其实我们只要理解SCHEDULE,PROGRAM和JOB这三个概念,就很容易学会如何使用它。
SCHEDULE表示调度计划表。指定调度从什么时间开始,什么时候结束,以什么频度调度。使用DBMS_SCHEDULER.CREATE_SCHEDULE过程创建SCHEDULE。
创建的SCHEDULE对象可以在DBA_SCHEDULER_SCHEDULES或USER_SCHEDULER_SCHEDULES视图中查看。
PROGRAM表示调度应该做什么事情,支持DMSQL块和存储过程。使用DBMS_SCHEDULER.CREATE_PROGRAM创建PROGRAM。创建的PROGRAM对象,可以在视图DBA_SCHEDULER_PROGRAMS或USER_SCHEDULER_PROGRAMS中查看。
JOB表示按照指定的SCHEDULE,执行指定PROGRAM,完成用户指定的工作。使用DBMS_SCHEDULER.CREATE_JOB创建JOB。创建的JOB对象,可以在DBA_SCHEDULER_JOBS或USER_SCHEDULER_JOBS视图中查看。
创建DBMS_SCHEDULER系统包
DM数据库安装完成之后默认不创建DBMS_SCHEDULER系统包,如果要使用该系统包需要手工创建,创建或删除DBMS_SCHEDULER系统包语法如下:
void SP_INIT_DBMS_SCHEDULER_SYS (
CREATE_FLAG int
)
参数详解
CREATE_FLAG为1时表示创建DBMS_SCHEDULER包;为0表示删除该系统包。
DBMS_SCHEDULER相关方法
CREATE_SCHEDULE过程
CREATE_SCHEDULE用于创建一个调度。语法如下:
PROCEDURE DBMS_SCHEDULER.CREATE_SCHEDULE(
SCHEDULE_NAME IN VARCHAR,
START_DATE IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
REPEAT_INTERVAL IN VARCHAR,
END_DATE IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
COMMENTS IN VARCHAR DEFAULT NULL
);
参数详解
SCHEDULE_NAME调度名称。要求在模式下名称唯一,不能为空。
START_DATE调度第一次有效日期或者时间。对于重复的调度,START_DATE是个参照值,这种情况START_DATE不是调度的开始时间,调用的开始决定于REPEATE_INTERVAL的设置。
REPEATE_INTERVAL调度重复间隔,用于指定调用隔多久重复一次。它是基于日历语法的表达式。
END_DATE作业停止运行时间。如果为未设置,作业则一直有效。
COMMENTS调度相关的描述或注释。默认为空。
CREATE_PROGRAM过程
CREATE_PROGRAM过程用于创建一个程序。语法如下:
PROCEDURE DBMS_SCHEDULER.CREATE_PROGRAM(
PROGRAM_NAME IN VARCHAR,
PROGRAM_TYPE IN VARCHAR,
PROGRAM_ACTION IN VARCHAR,
NUMBER_OF_ARGUMENTS IN INT DEFAULT 0,
ENABLED IN BOOLEAN DEFAULT FALSE,
COMMENTS IN VARCHAR DEFAULT NULL
);
参数详解
PROGRAM_NAME程序名称。不能为空,要求在包模式下名称中名称唯一。
PROGRAM_TYPE程序类型。不能为空,支持类型如下:PLSQL_BLOCK:说明程序是一个DMSQL语句块,即DM的SQL程序设计里的语句块。此类型的程序或者作业不允许设置参数个数,也就是说设置的参数个数必须为0;STORED_PROCEURE:说明程序是一个存储过程。支持存储过程,不支持带有返回值的函数。包含输入输出或者输出参数的存储过程不支持。
PROGRAM_ACTION定义程序的动作。不能为空。可能的动作如下:对于DMSQL语句块,程序动作就是去执行DMSQL代码。对于存储过程,程序动作是存储过程的名称。如果存储过程与作业不属于同一个模式,则需要指定存储过程名称的时候,指定模式名。
NUMBER_OF_ARGUMENTS定义程序包含的参数的个数。若未设置,则默认为0。一个程序最多可以指定255个参数。
ENABLED指定程序是否以激活的方式创建。若设置为TRUE,则执行合法性检测,检测成功,则创建程序并置ENABLED状态。默认设置为FALSE,程序不以激活的方式创建,可以在程序使用之前通过调用ENABLE过程来激COMMENTS程序的相关评论或者注释。默认为空。
CREATE_JOB过程
CREATE_JOB过程用来创建作业。支持三种方法创建JOB。
①不使用已经存在的程序(PROGRAM)或者调度(SCHEDULE)创建作业。语法如下:
PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME IN VARCHAR,
JOB_TYPE IN VARCHAR,
JOB_ACTION IN VARCHAR,
NUMBER_OF_ARGUMENTS IN INT DEFAULT 0,
START_DATE IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
REPEAT_INTERVAL IN VARCHAR DEFAULT NULL,
END_DATE IN TIMESTAMP WITH TIME ZONE 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,
CREDENTIAL_NAME IN VARCHAR DEFAULT NULL,
DESTINATION_NAME IN VARCHAR DEFAULT NULL
);
②使用命名程序(PROGRAM)和命名调度(SCHEDULE)创建作业。语法如下:
PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME IN VARCHAR,
PROGRAM_NAME IN VARCHAR,
SCHEDULE_NAME IN VARCHAR,
JOB_CLASS IN VARCHAR DEFAULT 'DEFAULT_JOB_CLASS',
ENABLED IN BOOLEAN DEFAULT FALSE,
AUTO_DROP IN BOOLEAN DEFAULT TRUE,
COMMENTS IN VARCHAR DEFAULT NULL,
JOB_STYLE IN VARCHAR DEFAULT 'REGULAR',
CREDENTIAL_NAME IN VARCHAR DEFAULT NULL,
DESTINATION_NAME IN VARCHAR DEFAULT NULL
);
③使用命名程序(PROGRAM)和内置调度(SCHEDULE)创建作业。语法如下:
PROCEDURE DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME IN VARCHAR,
PROGRAM_NAME IN VARCHAR,
START_DATE IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
REPEAT_INTERVAL IN VARCHAR DEFAULT NULL,
END_DATE IN TIMESTAMP WITH TIME ZONE 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,
JOB_STYLE IN VARCHAR DEFAULT 'REGULAR',
CREDENTIAL_NAME IN VARCHAR DEFAULT NULL,
DESTINATION_NAME IN VARCHAR DEFAULT NULL
);
参数详解
JOB_NAME 作业名称。要求在包模式下名称中名称唯一,不能为空。可以使用GENERATE_JOB_NAME过程自动生成一个作业名。
JOB_TYPE、JOB_ACTION同CREATE_PROGRAM中PROGRAM_TYPE、PROGRAM_ACTION参数。不能为空。
PROGRAM_NAME,SCHEDULE_NAME 作业使用的PROGRAM和调度名称。
START_DATE,END_DATE、REPEAT_INTERVAL作业起始、结束日期和执行周期间隔。如果 START_DATE 和 REPEAT_INTERVAL 都为 NULL,表示作业在 ENABLE后立即执行。REPEAT_INTERVAL仅支持日历语法格式。
ENABLED 指定作业创建时是否启用。
AUTO_DROP 如果为TRUE,作业自动删除。默认为TRUE。自动删除的条件如下:①作业的结束日期已经过期;②作业不是重复执行作业,并且只执行一次。如果为NULL,则取值为FALSE,作业不会自动删除。
JOB_CLASS,JOB_STYLE,CREDENTIAL_NAME,DESTINATION_NAME不支持,只作兼容参数用。
日历表达式
日历表达式是Linux系统的crontab使用的格式,日历表达式分为三部分:
第一部分是频率,即“FREQ”关键字,它是必须指定的;取值YEARLY| MONTHLY | WEEKLY | DAILY | HOURLY | MINUTELY | SECONDLY。
第二部分是时间间隔,即“INTERVAL”关键字,取值范围是1-999。它是可选参数;
第三部分是附加的参数,可用于精确地指定日期和时间,也是可选参数。下面这些值都是合法的:BYMONTH,BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOND。
举几个简单的日历写法。例如:
每隔10分钟运行一次:
repeat_interval=> 'FREQ=MINUTELY; INTERVAL=10'
每天1点运行一次
repeat_interval=> 'FREQ=DAILY; BYHOUR=1'
每周的1,3,5运
repeat_interval=> 'FREQ=WEEKLY; BYDAY=MON,WED,FRI"
每年的3,6,9,12月的1号运行:
repeat_interval=> 'FREQ=YEARLY; BYMONTH=MAR,JUN,SEP,DEC; BYMONTHDAY=1'
每月的最后一天执行一次:
repeat_interval=> 'FREQ=MONTHLY; BYMONTHDAY=-1’
使用方法
前面介绍那么多,下面分别从CREATE_JOB的三种方法上介绍JOB的创建。实现步骤参考如下:
1.创建DBMS_SCHEDULER系统包
调用SP_INIT_DBMS_SCHEDULER_SYS创建DBMS_SCHEDULER系统包:
SP_INIT_DBMS_SCHEDULER_SYS(1);
创建完成之后,数据库模式下面多了SCHEDULER模式,该模式属于SYSDBA用户,SCHEDULER模式拥有DBMS_SCHEDULER系统包、相关表和视图。如下图所示。
2.赋予用户相关权限
调用DBMS_SCHEDULER系统包需要有执行该包的权限,使用管理员SYSDBA用户赋予DMHR用户该权限。语句参考如下:
grant execute on dbms_scheduler to dmhr;
3.创建程序和作业
创建存储过程P_TEST_SCHEDULER往该表插入数据:
create or replace procedure p_test_scheduler
as
begin
insertintot_test(inserttime,jobtype)
values(sysdate,'DBMS_SCHEDULER');
commit;
end;
①不使用程序和调度来创建JOB
创建TEST_SCHEDULERJOB1作业,直接使用job_action、job_type、repeat_interval指定作业的类型和动作和执行周期(每10分钟执行一次,30秒时执行)。
begin
dbms_scheduler.create_job(
job_name => 'test_schedulerjob1',
job_type => 'STORED_PROCEDURE',
job_action => 'p_test_scheduler',
repeat_interval => 'FREQ=minutely;INTERVAL=10;bysecond=30',
enabled => TRUE);
commit;
end;
②使用程序、不使用调度来创建JOB
创建程序TEST_PROGRAM调用P_TEST_SCHEDULER存储过程:
begin
dbms_scheduler.create_program(
program_name => 'TEST_PROGRAM',
program_type => 'STORED_PROCEDURE',
program_action => 'p_test_scheduler',
enabled => TRUE);
commit;
end;
创建TEST_SCHEDULERJOB2作业,调用TEST_PROGRAM程序,每小时10分时运行一次:
begin
dbms_scheduler.create_job(
job_name => 'test_schedulerjob2',
program_name => 'TEST_PROGRAM',
start_date => null,
repeat_interval => 'FREQ=hourly;INTERVAL=1;byminute=10',
enabled => TRUE);
end;
③使用程序、调度来创建JOB
创建调度TEST_SCHEDULER指定调度周期和间隔(每天10点运行一次):
begi
dbms_scheduler.create_schedule(
schedule_name => 'TEST_SCHEDULER',
start_date => null,
repeat_interval => 'FREQ=daily;INTERVAL=1;byhour=10',
end_date => null);
commit;
end;
创建TEST_SCHEDULERJOB3作业,根据TEST_PROGRAM程序和TEST_SCHEDULER调度来执行作业:
begin
dbms_scheduler.create_job(
job_name => 'test_schedulerjob3',
program_name => 'TEST_PROGRAM',
schedule_name => 'TEST_SCHEDULER',
enabled => TRUE);
commit;
end;
语句执行完成,查看USER_SCHEDULER_开头的视图中可以看到上述步骤创建的JOB、PROGRAM、SCHEDULER。
使用如下语句查看USER_SCHEDULER_JOBS视图,可以看到我们新建的三个JOB:
selectt.JOB_NAME,t.JOB_ACTION,t.ENABLED,t.START_DATE,t.PROGRAM_NAME,t.SCHEDULE_NAME,t.REPEAT_INTERVAL
from user_scheduler_jobs t;
DBMS_SCHEDULER创建的JOB运行日志和详情可以在USER_SCHEDULER_JOB_LOG和USER_SCHEDULER_JOB_RUN_DETAILS及对应的DBA开头的视图中查询。DBMS_JOB没有相关的JOB运行日志,这也是DBMS_SCHEDULER比DBMS_JOB强大的地方。
DBMS_SCHEDULER还具有创建、修改、删除、禁用JOB/SCHEDULER/PROGRAM、邮件通知等功能,其他功能请参考《DM8系统包使用手册》。