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,只需要指定该参数就可创建chainrule_set_name IN VARCHAR2 DEFAULT NULL, evaluation_interval IN INTERVAL DAY TO SECOND DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
ParameterDescription
chain_nameThe 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_nameIn 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 and DROP_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_intervalIf 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.
commentsAn 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;
/
