Job Chains

最新推荐文章于 2021-04-13 05:52:01 发布

jgmydsai

最新推荐文章于 2021-04-13 05:52:01 发布

阅读量825

点赞数

分类专栏： DBMS_SCHEDULER

本文链接：https://blog.csdn.net/jgmydsai/article/details/38640587

版权

DBMS_SCHEDULER 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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_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