Oracle10g任务调度创建步骤

编辑器加载中...

/* 创建可执行程序 */
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/1161983
Oracle任务调度之基于时间的任务调度（原创） http://czmmiao.iteye.com/blog/1003505