ORACLE HANDBOOK系列之十三：计划任务（Scheduler）

10G中引入了SCHEDULER，其强大的功能远超9I中的JOB，比如在其定期执行任务时引入的repeat_interval，使得我们可以极其自由地设置任务的执行时间；比如其允许ORACLE执行外部程序或调用操作系统命令；又比如新引入的事件、CHAIN、时间窗等高级概念，本文演示其基本用法，想深入研究的可移步君三思文章http://www.5ienet.com/note/html/scheduler/）

1. 简单回顾一下9i中Job的用法

declare
  jid number;
begin
  dbms_job.submit(job      => jid,
                  what     => 'begin insert into old_job_test values(sysdate, ''abc''); commit; end;',
                  interval => 'trunc(sysdate,''mi'')+3/24/60');
  commit;
  dbms_output.put_line(jid);
end;

这里我们创建了一个Job，任务是向一张表中插入当前时间，这个Job每3分钟执行一次任务。其中输出的jid即job id，我们可以查看字典表select * from user_jobs where job={job id}查看此Job的一些具体信息。

2. Scheduler的使用

我们看看用新的Scheduler如何创建一个定时任务：

begin
  dbms_scheduler.create_job(job_name        => 'job1',
                            enabled         => true,
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'begin insert into new_job_test values(sysdate, ''abc''); commit; end;',
                            repeat_interval => 'FREQ=MINUTELY;INTERVAL=1');
end;

这是Scheduler最简单的用法，创建完成后，可以通过select * from user_scheduler_jobs where job_name='{job name}' 来查看此job的一些具体信息。嗯，看起来跟9i中的Job没有什么区别？别着急，我们再来看看Scheduler另一个例子：

View Code

--创建示例所需要的表及存储过程----------------------------------------------------
--CREATE A SCHEDULER LOG TABLE
CREATE TABLE scheduler_log_0306(c1 NUMBER, c2 NUMBER, c3 VARCHAR2(30));

--CREATE A PROCEDURE
CREATE OR REPLACE PROCEDURE proc_0306(pi1 IN NUMBER,
                                      pi2 IN NUMBER,
                                      pi3 IN VARCHAR2)
IS
BEGIN
  INSERT INTO scheduler_log_0306 VALUES(pi1,pi2,pi3);
  COMMIT;
END;

--创建一个Program对象-------------------------------------------------------
--CREATE A PROGRAM
BEGIN
  DBMS_SCHEDULER.create_program(program_name => 'program_0306',
                                program_type => 'STORED_PROCEDURE',
                                program_action => 'PROC_0306',
                                number_of_arguments => 3);
END;

BEGIN
  --ADD PARAMETER 1, WITHOUT DEFAULT VALUE
  DBMS_SCHEDULER.define_program_argument(program_name => 'program_0306',
                                         argument_position => 1,
                                         argument_name => 'pi1',
                                         argument_type => 'NUMBER',
                                         out_argument => FALSE);

  --ADD PARAMETER 2, WITH DEFAULT VALUE
  DBMS_SCHEDULER.define_program_argument(program_name => 'program_0306',
                                         argument_position => 2,
                                         argument_name => 'pi2',
                                         argument_type => 'NUMBER',
                                         default_value => 77,
                                         out_argument => FALSE);

  --ADD PARAMETER 3, USE PREDEFINED METADATA AS ITS DEFAULT VALUE
  DBMS_SCHEDULER.define_metadata_argument(program_name => 'program_0306',
                                          argument_position => 3,
                                          argument_name => 'pi3',
                                          metadata_attribute => 'job_name');
END;

--ENABLE THE PROGRAM
BEGIN
  DBMS_SCHEDULER.enable(name => 'program_0306');
END;

--DROP A PROGRAM
/*BEGIN
  DBMS_SCHEDULER.drop_program(program_name => 'program_0306');
END;*/

--创建一个Scheduler对象-------------------------------------------------------
--CREATE A SCHEDULER
BEGIN
  DBMS_SCHEDULER.create_schedule(schedule_name => 'scheduler_0306',
                                 repeat_interval => 'FREQ=MINUTELY;INTERVAL=2');
END;

--YOU CAN'T AND YOU DON'T NEED TO ENABLE THE SCHEDULER
/*BEGIN
  DBMS_SCHEDULER.enable(name => 'scheduler_0306');
END;*/

--DROP A PROGRAM
/*BEGIN
  DBMS_SCHEDULER.drop_schedule(schedule_name => 'scheduler_0306');
END;*/

 

--创建一个Job对象----------------------------------------------------------
--CREATE A JOB
BEGIN
  DBMS_SCHEDULER.create_job(job_name => 'job_0306',
                            program_name  => 'program_0306',
                            schedule_name => 'scheduler_0306');                            
  --
  DBMS_SCHEDULER.set_job_argument_value(job_name => 'job_0306',
                                        argument_position => 1,
                                        argument_value => 100);
END;

--ENABLE THE JOB
BEGIN
  DBMS_SCHEDULER.enable(name => 'job_0306');
END;

--DROP A JOB
/*BEGIN
  DBMS_SCHEDULER.drop_job(job_name => 'job_0306');
END;*/

我们可以看到，新的scheduler技术中，原来的job被分离成3个对象，一个是program，即要做什么；一个scheduler，即何时做；一个job，用于关联program与scheduler。可以查看以下视图来检查scheduler的状态，所有与scheduler相关的信息可以通过user/all/dba_scheduler_*视图中，例如下列最后一个视图就存有job每一次执行的具体日志，这对于检查日志的运行非常有用。

--CHECK DICTIONARY TABLES
SELECT * FROM user_scheduler_programs;
SELECT * FROM user_scheduler_schedulers;
SELECT * FROM user_scheduler_jobs;
SELECT * FROM user_scheduler_job_log;

本例中，我们还可以查看测试时建立的scheduler_log_0306表，看看数据是否进来了：

--CHECK IF THE PROCEDURE WORKS 
SQL> select * from scheduler_log_0306;

        C1         C2 C3

---------- ---------- ------------------------------

       100         77 JOB_0306

有必要解释一下插入的这一行数据，c1列的100是我们通过set_job_argument_value指定的，这上好理解；而c2列的77则是通过define_program_argument设置的默认值；c3列为什么是’JOB_0306’，这跟define_program_argument的metadata_attribute参数有关，这里我们指定了job_name，即以job名称作为参数值。关于metadata_attribute可选的还包括：

JOB_NAME

JOB_OWNER

JOB_START

WINDOW_START

WINDOW_END

JOB_SUBNAME

EVENT_MESSAGE

JOB_SCHEDULED_START

另外，无法支持输出参数，所以define_metadata_argument 的out_argument参数要么不设置，要么设置成FALSE，如果设置为TRUE，则会出现：ORA-03001 unimplemented feature错误。

3. 重点介绍一下repeat_interval

FREQ=	YEARLY\|MONTHLY\|WEEKLY\|DAILY\|HOURLY\|MINUTELY\|SECONDLY
INTERVAL=	[1,99]
BYMONTH=	[1,12] or
	JAN\|FEB\|MAR\|APR\|MAY\|JUN\|JUL\|AUG\|SEP\|OCT\|NOV\|DEC
BYWEEKNO=	[1,53] or [-53,-1]
BYYEARDAY=	[1,366] or [-366,-1]
BYMONTHDAY=	[1,31] or [-31,-1]
BYDAY=	MON\|TUE\|WED\|THU\|FRI\|SAT\|SUN
BYHOUR=	[0,23]
BYMINUTE=	[0,59]
BYSECOND=	[0,59]

（表格的内容经过简化，详尽文档参见 doc http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72235）

Repeat_interval极其灵活强大，使得你可以实现几乎是任意的你想要的定期情况。它包括三个主要的部分：

第一部分即FREQ（频率），为必须项，

第二部分为INTERVAL（间隔），

第三部分为BY***，参见以下示例：

【以下主要引用：君三思文章http://www.5ienet.com/note/html/scheduler/oracle-scheduler-repeat_interval.shtml】

以下设置任务逢10分钟运行：

REPEAT_INTERVAL => 'FREQ=HOURLY; BYMINUTE=0,10,20,30,40,50; BYSECOND=0' 或

REPEAT_INTERVAL => 'FREQ=MINUTELY; BYMINUTE=0,10,20,30,40,50; BYSECOND=0'

以下设置任务仅在周5的时候运行：

REPEAT_INTERVAL => ‘FREQ=DAILY; BYDAY=FRI’; 或

REPEAT_INTERVAL => ‘FREQ=WEEKLY; BYDAY=FRI’; 或

REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDAY=FRI’;

以下设置任务隔一周运行一次，并且仅在周5运行：

REPEAT_INTERVAL => ‘FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI’;

以下设置任务在当月最后一天运行：

REPEAT_INTERVAL => ‘FREQ=MONTHLY; BYMONTHDAY=-1’;

以下设置任务在3月10日运行：

REPEAT_INTERVAL => ‘FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10’; 或

REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDATE=0310’;

以下设置任务每10隔天运行：

REPEAT_INTERVAL => ‘FREQ=DAILY; INTERVAL=10’;

以下设置任务在每天的下午4、5、6点时运行：

REPEAT_INTERVAL => ‘FREQ=DAILY; BYHOUR=16,17,18’;

以下设置任务在每月29日运行：

REPEAT_INTERVAL => ‘FREQ=MONTHLY; BYMONTHDAY=29’;

以下设置任务在每年的最后一个周5运行：

REPEAT_INTERVAL => ‘FREQ=YEARLY; BYDAY=-1FRI’;

以下设置任务每隔50个小时运行：

REPEAT_INTERVAL => ‘FREQ=HOURLY; INTERVAL=50’;

4. 其它

1）如何使得JOB只执行一次，create_scheduler时设置repeat_interval为null，参见：

BEGIN
  dbms_scheduler.create_schedule(repeat_interval => null,
                                 start_date      => systimestamp,
                                 schedule_name   => 'scheduler_0306_2');

  dbms_scheduler.create_job(job_name      => 'job_0306_2',
                            program_name  => 'program_0306',
                            schedule_name => 'scheduler_0306_2',
                            auto_drop     => FALSE,
                            enabled       => TRUE);
END;

这时候，如果你查看user_scheduler_schedules视图，会发现新建的scheduler_0306_2的schedule_type是’once’, 而不是’calendar’。

2）关于user_scheduler_jobs中的STATE

SCHEDULED：表示正在等待下次执行，此时你可以看到正常的NEXT_RUN_TIME

RUNNING：表示正处在某次的执行中，通常在program比较耗时的情况下容易看到此状态，这种情况下你看到的NEXT_RUN_TIME是本次执行开始的时间，而不是下次。

posted @ 2012-09-03 15:28 后厂村思维导图馆阅读(2574) 评论(0) 编辑收藏举报

刷新页面返回顶部

后厂村思维导图馆

ORACLE HANDBOOK系列之十三：计划任务（Scheduler）

公告