11g调度--chain的使用

官网说明：

CREATE_CHAIN Procedure

This procedure creates a new chain. The chain name can be optionally qualified with a schema name (for example,myschema.myname).

这个过程用于创建一个新的链！链名可以这么写：user_name.chain_name

A chain is always created as disabled and must be enabled with the ENABLE Procedure before it can be used.

链总是disabled的，需要用 dbms_scheduler.enble('chain_name')来enabled

DBMS_SCHEDULER.CREATE_CHAIN (   chain_name              IN VARCHAR2,    只需要指定该参数就可创建chain   rule_set_name           IN VARCHAR2 DEFAULT NULL,   evaluation_interval     IN INTERVAL DAY TO SECOND DEFAULT NULL,   comments                IN VARCHAR2 DEFAULT NULL);

ParameterDescription

chain_name

The name to assign to the new chain, which can optionally be qualified with a schema. This must be unique in the SQL namespace, therefore, there cannot already be a table or other object with this name and schema.

在同一schema下 不能有其他对象和chain名一样

rule_set_name

In the normal case, no rule set should be passed in. The Scheduler automatically creates a rule set and associated empty evaluation context. You then useDEFINE_CHAIN_RULE to add rules andDROP_CHAIN_RULE to remove them.

默认情况下，调度程序创建一个空的规则集合。你可以用 DEFINE_CHAIN_RULE来定义 或者 用DROP_CHAIN_RULE 删除规则集合

Advanced users can create a rule set that describes their chain dependencies and pass it in here. This allows greater flexibility in defining rules. For example, conditions can refer to external variables, and tables can be exposed through the evaluation context. If you pass in a rule set, you must ensure that it is in the format of a chain rule set. (For example, all steps must be listed as variables in the evaluation context). If no rule set is passed in, the rule set created is of the formSCHED_RULESET${N} and the evaluation context created is of the form SCHED_EVCTX${N}




See Oracle Streams Concepts and Administration for information on rules and rule sets.

evaluation_interval

If this is NULL, reevaluation of the rules of a running chain are performed only when the job starts and when a step completes. A non-NULL value causes rule evaluations to also occur periodically at the specified interval. Because evaluation may be CPU-intensive, this should be conservatively set to the highest possible value or left atNULL if possible.evaluation_interval cannot be less than a minute or greater than a day.

comments

An optional comment describing the purpose of the chain

下面附上例子：

步骤1：创建chain 
--a chain is always created as disabled and must be enabled with theenable procedure before it can be used.
begin
dbms_scheduler.create_chain('procedure_chain');
     dbms_scheduler.enable('procedure_chain');
end;
/


步骤2：
以下是级联删除chain下的rule和step
exec dbms_scheduler.drop_chain('procedure_chain',true);


/*批量创建program的脚本。注意要有create job权限！*/
create or replace procedure create_my_program
as
  p_v_name varchar2(60);
begin
  --删除program
     for v_pro in(
         select program_name from user_scheduler_programs order by program_name
         )loop
              dbms_scheduler.drop_program(v_pro.program_name);
         end loop;
  --创建program
     for v_name in(
         select object_name from user_procedures where object_name like 'insert%' order by object_name
         )loop
    p_v_name := substr(v_name.object_name,8)||'_pro';
      dbms_scheduler.create_program(
                                       program_name => p_v_name,
                                       program_type => 'stored_procedure',
                                       program_action => v_name.object_name,
                                       enabled => true
                                         );  
    end loop;
end;
/
--------------------------------------------


批量创建步骤step
create or replace procedure create_my_step
as
begin
--删除step
     for v_pro in(
         select step_name from user_scheduler_chain_steps order by step_name
         )loop
              dbms_scheduler.drop_chain_step('procedure_chain',v_pro.step_name);
         end loop;
  --创建step
     for v_name in(
         select program_name from user_scheduler_programs order by program_name
         )loop
      dbms_scheduler.define_chain_step(
                             chain_name => 'procedure_chain',
                             step_name => v_name.program_name||'_s',
                             program_name => v_name.program_name
                                 );  
    end loop;
end;
/




----------------------------------

创建chain的规则：rule


----第一步
begin
dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'true',
action => 'start campus_data_y_pro_s',
rule_name => 'campus_data_y_start_r'
);
----开始执行campus_data_y


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'campus_data_y_pro_s completed',
action => 'start campus_report_y_pro_s',
rule_name => 'campus_report_y_r'
);
----当campus_data_y执行完成，执行campus_report_y


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'campus_data_y_pro_s completed',
action => 'start campus_report_m_pro_s',
rule_name => 'campus_report_m_r'
);
----当campus_data_y执行完成，执行campus_report_m


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'campus_report_m_pro_s completed',
action => 'start area_data_m_pro_s',
rule_name => 'area_data_m_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'area_data_m_pro_s completed',
action => 'start area_data_y_pro_s',
rule_name => 'area_data_y_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'area_data_y_pro_s completed',
action => 'start area_report_m_pro_s',
rule_name => 'area_report_m_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'area_report_m_pro_s completed',
action => 'start area_report_y_pro_s',
rule_name => 'area_report_y_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'area_report_y_pro_s completed',
action => 'start city_data_m_pro_s',
rule_name => 'city_data_m_r'
);




dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'city_data_m_pro_s completed',
action => 'start city_data_y_pro_s',
rule_name => 'city_data_y_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'city_data_y_pro_s completed',
action => 'start city_report_m_pro_s',
rule_name => 'city_report_m_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'city_report_m_pro_s completed',
action => 'start city_report_y_pro_s',
rule_name => 'city_report_y_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'city_report_y_pro_s completed',
action => 'start province_data_m_pro_s',
rule_name => 'province_data_m_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'province_data_m_pro_s completed',
action => 'start province_data_y_pro_s',
rule_name => 'province_data_y_r'
);




dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'province_data_y_pro_s completed',
action => 'start province_report_m_pro_s',
rule_name => 'province_report_m_r'
);


dbms_scheduler.define_chain_rule(
chain_name => 'procedure_chain',
condition => 'province_report_m_pro_s completed',
action => 'start province_report_y_pro_s',
rule_name => 'province_report_y_r'
);


---------------------------------------------
dbms_scheduler.define_chain_rule (
chain_name => 'procedure_chain',
condition => 'province_report_y_pro_s completed',
action => 'end 0',
rule_name => 'end_r'
);
-----执行province_report_y完成 关闭！
end;
/


测试：
delete province_report_m where f_time > to_date('2013-02-01','yyyy-mm-dd hh24:mi:ss');
-----手动运行chain  exec dbms_scheduler.run_chain('procedure_chain','campus_report_m_pro_s');


创建job：每天晚上2点执行
begin
dbms_scheduler.create_job(
job_name => 'exec_procedure_in_up',
job_type => 'chain',
job_action => 'procedure_chain',
repeat_interval => 'freq=daily;byhour=2',
enabled => true
);
end;
/