DBMS_SCHEDULER
定时调用存贮过程:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'update_bb', //job的名字
job_type => 'STORED_PROCEDURE', //job的类型为“执行存储过程”
job_action => 'OPS.SALES_PKG.UPDATE_SALES_SUMMARY', //存储过程的名字
start_date => '28-APR-10 07.00.00 PM Australia/Sydney', //这里把时区去掉也可以!
repeat_interval => 'FREQ=DAILY;INTERVAL=2', /* every other day */
enabled =>true, //启动该job
comments => 'My new job'); //注释
END;
定时执行SQL:
BEGIN
BMS_SCHEDULER.CREATE_JOB (
job_name => 'zbb_job3',
job_type => 'PLSQL_BLOCK', //job的类型是执行sql语句
job_action => 'insert into bb values(1,sysdate);',
start_date => sysdate,
repeat_interval => 'freq = minutely; interval=1', //每分钟执行一次
enabled => true,
comments => 'my love');
ND;
创建执行计划,往计划里面增加任务:
创建计划:
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE ( //创建计划任务
schedule_name => 'zbb_schedule',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', //执行间隔:每5分钟
comments => 'Every 5 MINUTS');
END;
增加任务:
BEGIN
DBMS_SCHEDULER.CREATE_JOB ( //创建job 并把它加入到scheduler里面
job_name => 'zbb_job2',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into bb values(1,sysdate);',
schedule_name => 'ZBB_SCHEDULE');
END;
增加任务:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'zbb_job3',
job_type => 'PLSQL_BLOCK',
job_action => 'insert into bb values(1,sysdate);',
schedule_name => 'ZBB_SCHEDULE');
END;
删除job:
BEGIN
DBMS_SCHEDULER.DROP_JOB ( //删除job;多个job间用逗号隔开
job_name => 'zbb_job2,zbb_job3',
force => TRUE);
END;
网友评论