oracle 中的chain job

1--演示环境
2scott@CNMMBO> select * from v$version where rownum<2;
3 
4BANNER
5----------------------------------------------------------------
6Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
7 
8--创建演示表及序列
9CREATE TABLE tb_schduler
10(
11   id        NUMBER (10) NOT NULL,
12   descr     VARCHAR2 (20) NOT NULL,
13   cr_date   DATE NOT NULL,
14   CONSTRAINT tb_schduler_pk PRIMARY KEY (id)
15);
16 
17CREATE SEQUENCE tb_schduler_seq;
18 
191、创建程序
20--下面定义了3个需要用到的程序program，注意这里的program不等同于procedure或者package，但是可以调用procedure或package
21--下面的program主要是用于插入记录到测试表
22BEGIN
23  DBMS_SCHEDULER.create_program (
24    program_name   => 'test_proc_1',
25    program_type   => 'PLSQL_BLOCK',  -->这里的类型定义为PLSQL_BLOCK，支持STORED PROCEDURE/EXECUTEABLE
26    program_action => 'BEGIN
27                         INSERT INTO tb_schduler (id, descr, cr_date)
28                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_1'', SYSDATE);
29                         COMMIT;
30                       END;',
31    enabled        => TRUE,
32    comments       => 'Program for first link in the chain.');
33 
34  DBMS_SCHEDULER.create_program (
35    program_name   => 'test_proc_2',
36    program_type   => 'PLSQL_BLOCK',
37    program_action => 'BEGIN
38                         INSERT INTO tb_schduler (id, descr, cr_date)
39                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_2'', SYSDATE);
40                         COMMIT;
41                       END;',
42    enabled        => TRUE,
43    comments       => 'Program for second link in the chain.');
44 
45  DBMS_SCHEDULER.create_program (
46    program_name   => 'test_proc_3',
47    program_type   => 'PLSQL_BLOCK',
48    program_action => 'BEGIN
49                         INSERT INTO tb_schduler (id, descr, cr_date)
50                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_3'', SYSDATE);
51                         COMMIT;
52                       END;',
53    enabled        => TRUE,
54    comments       => 'Program for last link in the chain.');
55END;
56/
57 
582、创建chain
59--创建chain比较简单，通常只需要定义一个chain名字即可，主要是用于关联后续定义rule及step
60BEGIN
61  DBMS_SCHEDULER.create_chain (
62    chain_name          => 'test_chain_1',   -->定义chain的名字
63    rule_set_name       => NULL,             -->可以指定规则集的名字
64    evaluation_interval => NULL,
65    comments            => 'A test chain.');
66END;
67/
68 
693、定义chain步骤
70--下面定义chain的每一个步骤以及其对应的program_name，也就是每一步需要做什么
71BEGIN
72  DBMS_SCHEDULER.define_chain_step (
73    chain_name   => 'test_chain_1',   --->chain的名字
74    step_name    => 'chain_step_1',   --->步骤地名字
75    program_name => 'test_proc_1');   --->当前步骤应执行的相应程序
76 
77  DBMS_SCHEDULER.define_chain_step (
78    chain_name   => 'test_chain_1',
79    step_name    => 'chain_step_2',
80    program_name => 'test_proc_2');
81 
82  DBMS_SCHEDULER.define_chain_step (
83    chain_name   => 'test_chain_1',
84    step_name    => 'chain_step_3',
85    program_name => 'test_proc_3');
86END;
87/
88 
894、定义chain规则
90--用于定义chain根据执行结果应该如何跳转的问题，每个CHAIN 规则都拥有condition和action 属性，
91--当满足condition 时则执行action中指定的step。使用DBMS_SCHEDULER.DEFINE_CHAIN_RULE 过程
92BEGIN
93  DBMS_SCHEDULER.define_chain_rule (
94    chain_name => 'test_chain_1',
95    condition  => 'TRUE',
96    action     => 'START "CHAIN_STEP_1"',
97    rule_name  => 'chain_rule_1',
98    comments   => 'First link in the chain.');
99 
100  DBMS_SCHEDULER.define_chain_rule (
101    chain_name => 'test_chain_1',
102    condition  => '"CHAIN_STEP_1" COMPLETED',
103    action     => 'START "CHAIN_STEP_2"',
104    rule_name  => 'chain_rule_2',
105    comments   => 'Second link in the chain.');
106 
107  DBMS_SCHEDULER.define_chain_rule (
108    chain_name => 'test_chain_1',
109    condition  => '"CHAIN_STEP_2" COMPLETED',
110    action     => 'START "CHAIN_STEP_3"',
111    rule_name  => 'chain_rule_3',
112    comments   => 'Third link in the chain.');
113 
114  DBMS_SCHEDULER.define_chain_rule (
115    chain_name => 'test_chain_1',
116    condition  => '"CHAIN_STEP_3" COMPLETED',
117    action     => 'END',
118    rule_name  => 'chain_rule_4',
119    comments   => 'End of the chain.');
120END;
121/
122 
1235、激活chain
124BEGIN
125  DBMS_SCHEDULER.enable ('test_chain_1');
126END;
127/
128 
1296、将chain添加到job
130BEGIN
131  DBMS_SCHEDULER.CREATE_JOB (
132    job_name        => 'test_chain_1_job',
133    job_type        => 'CHAIN',
134    job_action      => 'test_chain_1',
135    repeat_interval => 'freq=minutely; interval=2',
136    start_date      => SYSTIMESTAMP,
137    end_date        => SYSTIMESTAMP + (1/48),
138    enabled         => FALSE);   --->值为TRUE用于激活JOB 
139END;
140/
141 
1427、手动执行chain
143BEGIN
144  DBMS_SCHEDULER.run_chain (
145    chain_name    =>  'test_chain_1',
146    job_name      =>  'test_chain_1_run_job',
147    start_steps   =>  'chain_step_1,chain_step_3');  -->可以指定单步或多步以及所有步骤
148END;
149/
150 
151scott@CNMMBO> select * from tb_schduler;
152 
153        ID DESCR                CR_DATE
154---------- -------------------- -----------------
155         1 test_proc_1          20131203 14:36:03
156         2 test_proc_3          20131203 14:36:04
157 
158--激活job   
159scott@CNMMBO> exec dbms_scheduler.enable('test_chain_1_job');
160 
161PL/SQL procedure successfully completed.

参考自：https://blog.csdn.net/leshami/article/details/17096009