ORACLE HANDBOOK系列之十三:计划任务(Scheduler)

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

 1.    简单回顾一下9iJob的用法

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是本次执行开始的时间,而不是下次。 

原文地址:https://www.cnblogs.com/morvenhuang/p/2668891.html