Job Chains

来源：互联网发布：sql server安装包编辑：程序博客网时间：2024/06/08 11:50

Job Chains可以定制多个任务的先后顺序及分支结构

一、建立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_1TEST_PROGRAM_2TEST_PROGRAM_3TEST_PROGRAM_44 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_11 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_1TEST                           TEST_CHAIN_1                   CHAIN_STEP_2TEST                           TEST_CHAIN_1                   CHAIN_STEP_3TEST                           TEST_CHAIN_1                   CHAIN_STEP_44 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'           END5 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_12014-08-17 17:37:01  test_program_42014-08-17 17:38:00  test_program_12014-08-17 17:38:00  test_program_42014-08-17 17:39:00  test_program_12014-08-17 17:39:00  test_program_46 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 210ORA-06512: 在 "SYS.DBMS_SCHEDULER", line 557ORA-06512: 在 line 1SQL> EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');PL/SQL procedure successfully completedSQL> EXEC DBMS_SCHEDULER.drop_chain (chain_name  => 'test_chain_1');PL/SQL procedure successfully completedSQL> EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_1');PL/SQL procedure successfully completedSQL> EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_2');PL/SQL procedure successfully completedSQL> EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_3');PL/SQL procedure successfully completedSQL> EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_4');PL/SQL procedure successfully completedSQL> DROP TABLE t_job_test PURGE;Table dropped

0 0