大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

OracleJobChain

Job Chains

成都创新互联企业建站,十余年网站建设经验,专注于网站建设技术,精于网页设计,有多年建站和网站代运营经验,设计师为客户打造网络企业风格,提供周到的建站售前咨询和贴心的售后服务。对于成都做网站、成都网站制作中不同领域进行深入了解和探索,创新互联在网站建设中充分了解客户行业的需求,以灵动的思维在网页中充分展现,通过对客户行业精准市场调研,为客户提供的解决方案。

创建chain的顺序

  1. 创建chain object;

  2. 定义chain中的steps;

  3. 增加规则;

  4. 启用chain;

  5. 创建指向这个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


网站栏目:OracleJobChain
文章转载:http://dzwzjz.com/article/jiijdj.html
在线咨询
服务热线
服务热线:028-86922220
TOP