--创建演示表及序列

CREATE TABLE tb_schduler

(

id NUMBER (10) NOT NULL,

descr VARCHAR2 (20) NOT NULL,

cr_date DATE NOT NULL,

CONSTRAINT tb_schduler_pk PRIMARY KEY (id)

);

CREATE SEQUENCE tb_schduler_seq;

--1、创建程序

BEGIN

DBMS_SCHEDULER.create_program (

program_name => 'test_proc_1',

program_type => 'PLSQL_BLOCK', -->这里的类型定义为PLSQL_BLOCK，支持STORED PROCEDURE/EXECUTEABLE

program_action => 'BEGIN

INSERT INTO tb_schduler (id, descr, cr_date)

VALUES (tb_schduler_seq.NEXTVAL,''test_proc_1'', SYSDATE);

COMMIT;

END;',

enabled => TRUE,

comments => 'Program for first link in the chain.');

DBMS_SCHEDULER.create_program (

program_name => 'test_proc_2',

program_type => 'PLSQL_BLOCK',

program_action => 'BEGIN

INSERT INTO tb_schduler (id, descr, cr_date)

VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_2'', SYSDATE);

COMMIT;

END;',

enabled => TRUE,

comments => 'Program for second link in the chain.');

DBMS_SCHEDULER.create_program (

program_name => 'test_proc_3',

program_type => 'PLSQL_BLOCK',

program_action => 'BEGIN

INSERT INTO tb_schduler (id, descr, cr_date)

VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_3'', SYSDATE);

COMMIT;

END;',

enabled => TRUE,

comments => 'Program for last link in the chain.');

END;

--2、创建chain

BEGIN

DBMS_SCHEDULER.create_chain (

chain_name => 'test_chain_1', -->定义chain的名字

rule_set_name => NULL, -->可以指定规则集的名字

evaluation_interval => NULL,

comments => 'A test chain.');

END;

----3、定义chain步骤

BEGIN

DBMS_SCHEDULER.define_chain_step (

chain_name => 'test_chain_1', --->chain的名字

step_name => 'chain_step_1', --->步骤地名字

program_name => 'test_proc_1'); --->当前步骤应执行的相应程序

DBMS_SCHEDULER.define_chain_step (

chain_name => 'test_chain_1',

step_name => 'chain_step_2',

program_name => 'test_proc_2');

DBMS_SCHEDULER.define_chain_step (

chain_name => 'test_chain_1',

step_name => 'chain_step_3',

program_name => 'test_proc_3');

END;

--4、定义chain规则

BEGIN

DBMS_SCHEDULER.define_chain_rule (

chain_name => 'test_chain_1',

condition => 'TRUE',

action => 'START "CHAIN_STEP_1"',

rule_name => 'chain_rule_1',

comments => 'First link in the chain.');

DBMS_SCHEDULER.define_chain_rule (

chain_name => 'test_chain_1',

condition => '"CHAIN_STEP_1" COMPLETED',

action => 'START "CHAIN_STEP_2"',

rule_name => 'chain_rule_2',

comments => 'Second link in the chain.');

DBMS_SCHEDULER.define_chain_rule (

chain_name => 'test_chain_1',

condition => '"CHAIN_STEP_2" COMPLETED',

--可以改成条件为1、2都完成（CHAIN_STEP_1 COMPLETED AND CHAIN_STEP_2 COMPLETED），同时将规则2的条件改成TRUE

action => 'START "CHAIN_STEP_3"',

rule_name => 'chain_rule_3',

comments => 'Third link in the chain.');

DBMS_SCHEDULER.define_chain_rule (

chain_name => 'test_chain_1',

condition => '"CHAIN_STEP_3" COMPLETED',

action => 'END',

rule_name => 'chain_rule_4',

comments => 'End of the chain.');

END;

--5、激活chain

BEGIN

DBMS_SCHEDULER.enable ('test_chain_1');

END;

--6、将chain添加到job

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'test_chain_1_job',

job_type => 'CHAIN',

job_action => 'test_chain_1',

repeat_interval => 'freq=minutely; interval=2',

start_date => SYSTIMESTAMP,

end_date => SYSTIMESTAMP + (1/48),

enabled => FALSE); --->值为TRUE用于激活JOB

END;

--7、手动执行chain

BEGIN

DBMS_SCHEDULER.run_chain (

chain_name => 'test_chain_1',

job_name => 'test_chain_1_run_job',

start_steps => 'chain_step_1,chain_step_3'); -->可以指定单步或多步以及所有步骤

END;

select * from tb_schduler;

--激活job

exec dbms_scheduler.enable('test_chain_1_job');

--三、CHAIN相关状态及视图查询

job_chains --->数据字典dba_scheduler_chains

job_chain_steps --->数据字典dba_scheduler_chain_steps

job_chain_rules --->数据字典 dba_scheduler_chain_rules

job_log_detail --->数据字典dba_scheduler_job_run_details

select * from tb_schduler;

--四、移除CHAIN及相关JOB

对于与chain相关的program，rule，由于存在依赖性，因此需要先删除job，然后可以删除chain，program等

EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');

EXEC DBMS_SCHEDULER.drop_chain (chain_name => 'test_chain_1');

EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_proc_1');

EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_proc_2');

EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_proc_3');

 

--注：rule的条件有些复杂，网上搜索了一点应该够用了：

其语法看起来稍稍复杂一些，或者说是灵活，condition参数值支持下列的语法形式：

TRUE

FALSE

stepname[NOT]SUCCEEDED

stepname[NOT]FAILED

stepname[NOT]STOPPED

stepname[NOT]COMPLETED

stepname ERROR_CODE IN(integer,integer,integer...)

stepname ERROR_CODE NOT IN(integer,integer,integer...)

stepname ERROR_CODE=integer

stepname ERROR_CODE!=integer

stepname ERROR_CODE<>integer

stepname ERROR_CODE>integer

stepname ERROR_CODE>=integer

stepname ERROR_CODE<integer

stepname ERROR_CODE<=integer

甚至于，还可以制定成下列逻辑语法:

expression AND expression

expression OR expression

NOT(expression)