oracle 中的chain job

> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production --创建演示表及序列 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、创建程序 --下面定义了3个需要用到的程序program，注意这里的program不等同于procedure或者package，但是可以调用procedure或package --下面的program主要是用于插入记录到测试表 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 --创建chain比较简单，通常只需要定义一个chain名字即可，主要是用于关联后续定义rule及step 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步骤 --下面定义chain的每一个步骤以及其对应的program_name，也就是每一步需要做什么 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规则 --用于定义chain根据执行结果应该如何跳转的问题，每个CHAIN 规则都拥有condition和action 属性， --当满足condition 时则执行action中指定的step。使用DBMS_SCHEDULER.DEFINE_CHAIN_RULE 过程 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‘, 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; / scott@CNMMBO> select * from tb_schduler; ID DESCR CR_DATE ---------- -------------------- ----------------- 1 test_proc_1 20131203 14:36:03 2 test_proc_3 20131203 14:36:04 --激活job scott@CNMMBO> exec dbms_scheduler.enable(‘test_chain_1_job‘); PL/SQL procedure successfully completed.

 

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

oracle 中的chain job

标签：根据   run_job   value   begin   star   ted   acl   过程   varchar2