Job Chains可以定制多个任务的先后顺序及分支结构
一、建立program,供Chain调用
二、建立Chains
三、建立步骤
四、需要注意:上面的1 2 3 4只是step的名称,与实际的先后执行顺序无关,下面才是设定顺序的语句:
五,建立job调用Chain
可以看到,因为2失败,后面的chian走了program4
六、毁尸灭迹
一、建立program,供Chain调用
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO t_job_test(created, des)
VALUES (SYSDATE, ''test_program_1'');
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for first link in the chain.');
/*这儿制造点意外*/
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO t_job_test(created, des)
VALUES (1234567, ''test_program_2'');
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for second link in the chain.');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO t_job_test(created, des)
VALUES (SYSDATE, ''test_program_3'');
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for last link in the chain.');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_4',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO t_job_test(created, des)
VALUES (SYSDATE, ''test_program_4'');
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for last link in the chain.');
END;
/
SQL> SELECT program_name FROM Dba_Scheduler_Programs WHERE owner = 'TEST';
PROGRAM_NAME
------------------------------
TEST_PROGRAM_1
TEST_PROGRAM_2
TEST_PROGRAM_3
TEST_PROGRAM_4
4 rows selected
二、建立Chains
BEGIN
DBMS_SCHEDULER.create_chain (
chain_name => 'test_chain_1',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'A test chain.');
END;
/
SQL> SELECT owner,
2 chain_name
3 FROM dba_scheduler_chains;
OWNER CHAIN_NAME
------------------------------ ------------------------------
TEST TEST_CHAIN_1
1 row selected
三、建立步骤
BEGIN
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_1',
program_name => 'test_program_1');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_2',
program_name => 'test_program_2');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_3',
program_name => 'test_program_3');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_4',
program_name => 'test_program_4');
END;
/
SQL> SELECT owner, chain_name, step_name
2 FROM dba_scheduler_chain_steps
3 ORDER BY owner, chain_name, step_name;
OWNER CHAIN_NAME STEP_NAME
------------------------------ ------------------------------ ------------------------------
TEST TEST_CHAIN_1 CHAIN_STEP_1
TEST TEST_CHAIN_1 CHAIN_STEP_2
TEST TEST_CHAIN_1 CHAIN_STEP_3
TEST TEST_CHAIN_1 CHAIN_STEP_4
4 rows selected
四、需要注意:上面的1 2 3 4只是step的名称,与实际的先后执行顺序无关,下面才是设定顺序的语句:
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.');
/*1成功后执行2*/
dbms_scheduler.define_chain_rule(chain_name => 'test_chain_1',
condition => ':CHAIN_STEP_1.state = ''SUCCEEDED''',
action => 'START "CHAIN_STEP_2"',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
/*2成功后执行3*/
dbms_scheduler.define_chain_rule(chain_name => 'test_chain_1',
condition => ':CHAIN_STEP_2.state = ''SUCCEEDED''',
action => 'START "CHAIN_STEP_3"',
rule_name => 'chain_rule_3',
comments => 'Third link in the chain.');
/*2失败后执行4*/
dbms_scheduler.define_chain_rule(chain_name => 'test_chain_1',
condition => ':CHAIN_STEP_2.state = ''FAILED''',
action => 'START "CHAIN_STEP_4"',
rule_name => 'chain_rule_4',
comments => 'Third link in the chain.');
/*结束*/
dbms_scheduler.define_chain_rule(chain_name => 'test_chain_1',
condition => ':CHAIN_STEP_3.state = ''SUCCEEDED'' or :CHAIN_STEP_4.state = ''SUCCEEDED''',
action => 'END',
rule_name => 'chain_rule_5',
comments => 'End of the chain.');
END;
/
SQL> SELECT condition,
2 action
3 FROM dba_scheduler_chain_rules
4 ORDER BY owner, chain_name, rule_owner, rule_name;
CONDITION ACTION
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
TRUE START "CHAIN_STEP_1"
:CHAIN_STEP_1.state = 'SUCCEEDED' START "CHAIN_STEP_2"
:CHAIN_STEP_2.state = 'SUCCEEDED' START "CHAIN_STEP_3"
:CHAIN_STEP_2.state = 'FAILED' START "CHAIN_STEP_4"
:CHAIN_STEP_3.state = 'SUCCEEDED' or :CHAIN_STEP_4.state = 'SUCCEEDED' END
5 rows selected
五,建立job调用Chain
BEGIN
DBMS_SCHEDULER.enable ('test_chain_1');
END;
/
BEGIN
dbms_scheduler.create_job(job_name => 'test_chain_1_job',
job_type => 'CHAIN',
job_action => 'test_chain_1',
repeat_interval => 'freq=minutely; bysecond=0',
start_date => systimestamp,
end_date => systimestamp + (1 / 48),
enabled => TRUE);
END;
/
SQL> select * from t_job_test order by 1;
CREATED DES
-------------------- --------------------------------------------------
2014-08-17 17:37:00 test_program_1
2014-08-17 17:37:01 test_program_4
2014-08-17 17:38:00 test_program_1
2014-08-17 17:38:00 test_program_4
2014-08-17 17:39:00 test_program_1
2014-08-17 17:39:00 test_program_4
6 rows selected
可以看到,因为2失败,后面的chian走了program4
六、毁尸灭迹
SQL> EXEC DBMS_SCHEDULER.stop_job(job_name => 'test_chain_1_job');
begin DBMS_SCHEDULER.stop_job(job_name => 'test_chain_1_job'); end;
ORA-27366: 作业 "TEST.TEST_CHAIN_1_JOB" 不在运行
ORA-06512: 在 "SYS.DBMS_ISCHED", line 210
ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 557
ORA-06512: 在 line 1
SQL> EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');
PL/SQL procedure successfully completed
SQL> EXEC DBMS_SCHEDULER.drop_chain (chain_name => 'test_chain_1');
PL/SQL procedure successfully completed
SQL> EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_1');
PL/SQL procedure successfully completed
SQL> EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_2');
PL/SQL procedure successfully completed
SQL> EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_3');
PL/SQL procedure successfully completed
SQL> EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_4');
PL/SQL procedure successfully completed
SQL> DROP TABLE t_job_test PURGE;
Table dropped