Oracle dbms_scheduler建立帶輸入引數的JOB

abstractcyj發表於2014-05-05
Oracle
指令碼如下：
其中P_USER_ATTEND是需要在JOB裡呼叫的儲存過程，帶有兩個引數

BEGIN
  dbms_scheduler.create_program(program_name        => 'MY_ATTEND_PROGRAM',
                                program_type        => 'STORED_PROCEDURE',
                                program_action      => 'P_USER_ATTEND',
                                number_of_arguments => 2,
                                enabled             => FALSE,
                                comments            => '考勤');
  --宣告輸入引數
  dbms_scheduler.define_program_argument(program_name      => 'MY_ATTEND_PROGRAM',
                                         argument_name     => 'p_begintime',
                                         argument_position => 1,
                                         argument_type     => 'VARCHAR2',
                                         default_value     => '');

  dbms_scheduler.define_program_argument(program_name      => 'MY_ATTEND_PROGRAM',
                                         argument_name     => 'p_endtime',
                                         argument_position => 2,
                                         argument_type     => 'VARCHAR2',
                                         default_value     => '');

  dbms_scheduler.enable(NAME => 'MY_ATTEND_PROGRAM');

  dbms_scheduler.create_job(job_name        => 'JOBATTENDANCE',
                            program_name    => 'MY_ATTEND_PROGRAM',
                            start_date      => to_date('05-05-2014 10:47:00',
                                                       'dd-mm-yyyy hh24:mi:ss'), --job開始執行時間 
                            repeat_interval => 'FREQ=DAILY', --job執行頻率 
                            end_date        => NULL, --job結束執行時間 
                            enabled         => TRUE,
                            auto_drop       => FALSE,
                            comments        => '自動增加每天考勤'); --job描述 
  --給引數賦值
  dbms_scheduler.set_job_argument_value(job_name          => 'JOBATTENDANCE',
                                        argument_position => 1,   --引數序號
                                        argument_value    => '083000');

  dbms_scheduler.set_job_argument_value(job_name          => 'JOBATTENDANCE',
                                        argument_position => 2,
                                        argument_value    => '173000');
END;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-1154003/，如需轉載，請註明出處，否則將追究法律責任。

相關文章