--处理任务,定期的将SMS_PREPARE中超时纪录删除
procedure proc_job1
as
v_sqlerrm VARCHAR2(600);
BEGIN
--INSERT INTO LOG_INFO (USERNAME, LOG_TIME, ACTION) VALUES ('JOB1' ,sysdate,'JOB EXEC1') ;
LOOP
DELETE FROM SMS_PREPARE WHERE SEND_TIME < SYSDATE AND ROWNUM < 201;
EXIT WHEN SQL%NOTFOUND;
COMMIT;
END LOOP;
COMMIT;
--INSERT INTO LOG_INFO (USERNAME, LOG_TIME, ACTION) VALUES ('JOB1' ,sysdate,'JOB EXEC2') ;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := SUBSTR(SQLERRM,1,600);
INSERT INTO ERR_LOG(LOG_TIME,DETAILS)
VALUES(SYSDATE,'ERROR OCCUR IN PKG_JOBS.PROC_JOB1,REASON:' || v_sqlerrm);
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;
/*
删除任务
说明:
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;