本文主要介绍了Oracle数据库中JOB与存储过程的接合用法,具体示例请参考下文:
create or replace procedure TT is
begin
update infoopen
set checktype = 2
where floor(to_date(to_char(limittime, 'yyyy-MM-dd'),
'yyyy-MM-dd') -sysdate)<=5 and status<> 2 and checktype<>1; --显示即将超时
update infoopen
set isovertime = 1
where finishtime > limittime
and status = 2; --超时处理
update infoopen
set isovertime = 2
where finishtime < limittime
and status = 2; --没有超时处理
dbms_output.put_line('处理成功!');
end TT;
执行JOB
VARIABLE JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'TT;',SYSDATE,'SYSDATE+1');
COMMIT;
END;
/
BEGIN
DBMS_JOB.RUN(:JOBNO);
END;
/
移除JOB
exec dbms_job.remove(:JOB1);
exec dbms_job.remove(190);
COMMIT;
/
查看JOB
select * from dba_jobs;