drop table scheduler_test;

create table scheduler_test(x varchar2(200), y int);

truncate table scheduler_test;

select * from scheduler_test order by 1;

begin

  dbms_scheduler.drop_job('ttt');

  dbms_scheduler.purge_log(job_name => 'ttt');

end;

/

create or replace procedure p_1(p_x varchar2)is

begin

  insert into scheduler_test(x) values(p_x);

  commit;

end;

/

当 使用job_type=sp调用一个sp的时候，必须指定所有参数

因为没有定义program，所以只能用 argument_position指定

-- use stored_procedure as job_type and set arguments

declare

  l_x integer := 0;

begin

  dbms_scheduler.create_job(job_name        => 'ttt',

                            job_type    => 'stored_procedure',

                            job_action => 'p_1',

                            number_of_arguments => 1,

                            start_date      => systimestamp,

                            repeat_interval => 'freq=minutely;interval=1;bysecond=0,20,40');

  dbms_scheduler.set_job_argument_value('ttt',argument_position => 1,argument_value => to_char(systimestamp));  

  dbms_scheduler.enable('ttt');

end;

/

如 果job_type=plsql_block调用sp，不能通过set_job_argument_value指定参数

但是可以构造 字符串。并且可以使用Metadata Attributes来作为参数（如下的job_start）

-- use plsql_block as job_type and use scheduler metadata as arguments

declare

  l_x integer := 0;

begin

  dbms_scheduler.create_job(job_name        => 'ttt',

                            job_type    => 'plsql_block',

                            job_action => 'p_1(job_start);',

                            start_date      => systimestamp,

                            repeat_interval => 'freq=minutely;interval=1;bysecond=10');

  dbms_scheduler.enable('ttt');

end;

/