/* 创建可执行程序 */
begin
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'peace_sj_his.PROG_DATASYNC',
program_action => 'peace_sj_his.P_DATASYNC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 3,
comments => '数据同步程序',
enabled => false
);
end;
/
/* 设置可执行程序的输入参数 */
begin
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 1,
argument_type => 'VARCHAR2',
default_value => ''
);
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 2,
argument_type => 'VARCHAR2',
default_value => ''
);
DBMS_SCHEDULER.define_program_argument(
program_name => 'peace_sj_his.PROG_DATASYNC',
argument_position => 3,
argument_type => 'VARCHAR2',
default_value => ''
);
END;
/
/* 创建调度表 */
begin
DBMS_SCHEDULER.create_schedule(
schedule_name => 'peace_sj_his.SCH_DATASYNC',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => sysdate,
comments => '数据同步调度'
);
end;
/
/* 创建作业 */
begin
DBMS_SCHEDULER.create_job(
job_name => 'peace_sj_his.JOB_PPTN',
program_name => 'peace_sj_his.PROG_DATASYNC',
schedule_name => 'peace_sj_his.SCH_DATASYNC',
job_class => 'DEFAULT_JOB_CLASS',
comments => '雨量数据同步作业',
auto_drop => false,
enabled => false
);
end;
/
/* 启动可执行程序 */
exec DBMS_SCHEDULER.enable('PROG_DATASYNC');
/* 启动作业 */
exec DBMS_SCHEDULER.enable('JOB_PPTN');
/* 设置不同的作业参数 */
begin
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 1,
argument_value => 'ST_PPTN_R'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 2,
argument_value => 'TM'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'peace_sj_his.JOB_PPTN',
argument_position => 3,
argument_value => 'STCD'
);
end;
/
----Job管理-----------------------------------------------------------------------
/* 禁用Job */
exec dbms_scheduler.disable('JOBTEST');
/* 执行Job */
exec dbms_scheduler.run_job('JOBTEST');
/* 停止Job */
exec dbms_scheduler.stop_job('JOBTEST');
/* 删除Job */
exec dbms_scheduler.drop_job('JOBTEST');
----参考资料----------------------------------------------------------------------
Oracle 10g Scheduler 全面介绍 http://wallimn.iteye.com/blog/1161983Oracle任务调度之基于时间的任务调度(原创) http://czmmiao.iteye.com/blog/1003505