脚本如下:
其中 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;
其中 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8520577/viewspace-1154003/