Job Chains
创建chain的顺序
-
创建chain object；
-
定义chain中的steps；
-
增加规则；
-
启用chain；
-
创建指向这个chain的job。
创建Chain Object
实验描述：
ENV：create table test_chain(id number);
createtable test_chain1(id number);
createtable test_chain2(id number);
step1：test_chain1表插入一条数据；
step2：step1执行成功后，test_chain中有超过两条数据时，插入test_chain2表一条数据。
begin
dbms_scheduler.create_chain(chain_name=>’my_chain1′,rule_set_name=>null,evaluation_interval=>interval ‘1’ minute,
comments=>’Chain with 30 minuteevaluation interval’);
end;
/
PL/SQL procedure successfully completed.
evaluation_interval指的是chain rule的扫描间隔，这里是自定义的。Scheduler在启动job chain时和每次执行完chain step时都会去扫描chain rule，这里除了启动job chain时和chain step结束时，还会每一分钟自动扫描一次来判断rule的正确性。这个参数对于某种特殊情形，本次实验就启用这个参数。
定义Chain Steps
创建program
begin
dbms_scheduler.create_program(program_name=>’my_program1′,program_type=>’PLSQL_BLOCK’,
program_action=>’begin insert intotest_chain1 values (1);commit;end;’);
end;
/
PL/SQL procedure successfully completed.
begin
dbms_scheduler.create_program(program_name=>’my_program2′,program_type=>’PLSQL_BLOCK’,
program_action=>’begin insert intotest_chain2 values (2);commit;end;’);
end;
/
PL/SQL procedure successfully completed.
–enable program
SQL> begin
2 dbms_scheduler.enable(‘my_program1,my_program2’);
3 end;
4 /
创建step
begin
dbms_scheduler.define_chain_step(chain_name=>’my_chain1′,step_name=>’my_step1′,program_name=>’my_program1′);
dbms_scheduler.define_chain_step(chain_name=>’my_chain1′,step_name=>’my_step2′,program_name=>’my_program2′);
end;
/
PL/SQL procedure successfully completed.
Note：定义step时，program和chain可以不存在，但是启动chain时，必须保证program
和chain存在。
增加Chain规则
Rule描述：
step1直接执行；
step2执行条件：step1执行成功，并且(selectcount(*) from test_chain) >=2
begin
dbms_scheduler.define_chain_rule(chain_name=>’my_chain1′,condition=>’TRUE’,action=>’startmy_step1′,rule_name=>’rule_1′);
end;
/
PL/SQL procedure successfully completed.
begin
dbms_scheduler.define_chain_rule(chain_name=>’my_chain1′,condition=>’:my_step1.state=”SUCCEEDED”and (select count(*) from test_chain) >=2′,
action=>’startmy_step2′,rule_name=>’rule_1_2′);
end;
/
PL/SQL procedure successfully completed.
启用Chain
begin
dbms_scheduler.enable(‘my_chain1’);
end;
/
PL/SQL procedure successfully completed.
创建Chain job
Note：直接调用run_chain运行则不需enablechain，创建job则需要。
begin
dbms_scheduler.create_job(job_name=>’chain_job_1′,job_type=>’CHAIN’,job_action=>’my_chain1′,
start_date=>sysdate,repeat_interval=>’freq=minutely;interval=1′,enabled=>TRUE);
end;
/
Note：单独启动job，注意参数USE_CURRENT_SESSION=>false
SQL> begin
2 dbms_scheduler.run_job(job_name=>’chain_job_1′,USE_CURRENT_SESSION=>false);
3 end;
4 /
SQL> selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name=’CHAIN_JOB_1′;
JOB_NAME JOB_SUBNAME STATUS REQ_START_DATE ACTUAL_START_DATE
————— —————————— —————————————————————————————————-
CHAIN_JOB_1 STOPPED 16-JUN-16 03.44.03.000000 PM +08:00 16-JUN-16 03.44.03.588735 PM+08:00
CHAIN_JOB_1 MY_STEP1 FAILED 16-JUN-16 03.44.03.682508 PM +08:00 16-JUN-16 03.44.03.693946 PM+08:00
CHAIN_JOB_1 MY_STEP1 SUCCEEDED 16-JUN-16 04.17.43.335679 PM +08:00 16-JUN-16 04.17.43.436069 PM+08:00
发现my_step2还没有运行
SQL> select * from test_chain1;
ID
———-
1
my_program1已写入数据
制造my_step2启动的条件
SQL> insert into test_chainvalues(9);
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name=’CHAIN_JOB_1′;
JOB_NAME JOB_SUBNAME STATUS REQ_START_DATE ACTUAL_START_DATE
————— —————————— ————————————————– ————————————————–
CHAIN_JOB_1 STOPPED 16-JUN-16 03.44.03.000000 PM +08:00 16-JUN-16 03.44.03.588735 PM+08:00
CHAIN_JOB_1 MY_STEP2 SUCCEEDED 16-JUN-16 04.30.43.064558 PM +08:00 16-JUN-16 04.30.43.152496 PM+08:00
CHAIN_JOB_1 MY_STEP1 FAILED 16-JUN-16 03.44.03.682508 PM +08:00 16-JUN-16 03.44.03.693946 PM+08:00
CHAIN_JOB_1 MY_STEP1 SUCCEEDED 16-JUN-16 04.17.43.335679 PM +08:00 16-JUN-16 04.17.43.436069 PM+08:00
SQL> select * from test_chain2;
ID
———-
2
