/*
添加任务
说明:
1、根据数据库中的配置添加一个任务;
*/
procedure add_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
END;/*
删除任务
说明:
1、删除一个任务;
*/
procedure remove_job(iv_job in number)
as
BEGIN
dbms_job.REMOVE( iv_job );
END;/*
开始任务
说明:
1、根据数据库中的配置添加一个任务;
2、使用Run让任务开始执行;
*/
procedure start_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
dbms_job.run(v_job_id);
END;/*
重新开始任务
说明:
1、删除一个任务;
2、根据数据库中的配置添加一个任务;
3、使用Run让任务开始执行;
*/
procedure restart_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
dbms_job.remove(iv_job);
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
dbms_job.run(v_job_id);
END;end;
/
添加任务
说明:
1、根据数据库中的配置添加一个任务;
*/
procedure add_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
END;/*
删除任务
说明:
1、删除一个任务;
*/
procedure remove_job(iv_job in number)
as
BEGIN
dbms_job.REMOVE( iv_job );
END;/*
开始任务
说明:
1、根据数据库中的配置添加一个任务;
2、使用Run让任务开始执行;
*/
procedure start_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
dbms_job.run(v_job_id);
END;/*
重新开始任务
说明:
1、删除一个任务;
2、根据数据库中的配置添加一个任务;
3、使用Run让任务开始执行;
*/
procedure restart_job(iv_job in number)
as
v_job_id jobontime.job_id%TYPE;
v_job_what jobontime.JOB_WHAT%TYPE;
v_job_next_date jobontime.Job_next_date%TYPE;
v_job_interval jobontime.JOB_INTERVAL%TYPE;
BEGIN
dbms_job.remove(iv_job);
select job_id,job_what,job_next_date,job_interval
into v_job_id,v_job_what,v_job_next_date,v_job_interval
from jobontime
where job_id = iv_job; dbms_job.isubmit(v_job_id,v_job_what,to_date(v_job_next_date,'YYYYMMDDHH24MI'),v_job_interval);
dbms_job.run(v_job_id);
END;end;
/
解决方案 »
- 一个表关联问题
- oracle连接问题
- 怎么动态生成表名和列名???Oracle/sql不限
- 用java开发存储过程疑问
- 请教关于oracle设置定时的错误
- ORA-00600 错误,求大神指教
- 请问oracle表中的字段有“自动编号”这个数据类型吗?
- 怎么样对树形结构内部的层次进行排序?
- 关于SQL的简单问题?分数很丰厚!
- 急需!我要下载一些有关,惠谱型号为(HP PROLIANT ML 570)的硬件资料。还有关集群设置,双机容错(热备份)及基本配置单总过程(是用WIN
- 如何让oracle在明天晚上12点,或者每月的15号 执行一段存储过程? 是不是用jobs可以,如何写? 网上哪里有这方面资料?
- dbms_job无法定时执行!
BEGIN
--Do what you want
END;
/VARIABLE JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => :JOBNO,
what => 'TEST.DOIT;',
next_date => 待定,
interval => '待定',
no_parse => FALSE
);
COMMIT;
END;
/
PRINT JOBNO
所有的变动主要在 next_date 和 interval 参数,
你把上面的 待定 换成下面我给出的内容即可(注意:next_date参数没有引号,interval参数有引号):要求在以下时间定时执行一个存储过程,请问dbms_job怎么写?
(1)每月1日夜里2点30分定时执行ADD_MONTHS(TRUNC(SYSDATE,'MM'),1) + 2.5/24(2)每周星期一夜里2点30分定时执行
NEXT_DAY(TRUNC(SYSDATE),2)此处用2因为Oracle中星期日是1(3)每日夜里3点30分定时执行TRUNC(SYSDATE) + 3.5/24(4) 每隔2小时定时执行一个存储过程TRUNC(SYSDATE,'HH24')+2/24