oracle 定时任务

--创建视图,取最后一个审核的时间

create view  v_checkServiceLastTime
as
select * from (
select row_number() over(partition by  servicecode  order by checkdate desc  ) idx, 
servicecode ,checkdate ,checkstaus
 From  o_Servicecheck where  checkstaus='C1E' 
)a where idx =1

--获取未参加评价的数据
create  or replace  view v_NotPingjia
as
select * from v_checkServiceLastTime a where a.servicecode not in (
 select servicecode from  O_servicepingjia
)

 

 

--查询当天时间减去CHECKDATE时间为3天的数据 ,并执行INSERT语句
create or replace procedure pro_job_inserPingjia
is
userRow  v_NotPingjia%rowtype;
cursor userRows is select * from v_NotPingjia where sysdate -checkdate>3 ;
begin
  for userRow in  userRows loop
      insert into  o_servicepingjia(pjcode,fuwu,zhiliang,jiage,servicecode)
      values(servicepingjia_seq.nextVal,5,5 ,5,userRow.servicecode);
  end   loop;
end ;

--创建定时器,每天执行函数 pro_job_inserPingjia
--720分钟==12个小时执行一次
declare
v_jobid number;
begin
dbms_job.submit(v_jobid,'pro_job_inserPingjia;',sysdate,'trunc(sysdate+720/1440,''mi'')');
        commit;
end;

 

 

--查看任务队列
select job ,next_date,next_sec,failures, broken from user_jobs ;

select * from user_jobs;


--停止任务

begin
  dbms_job.remove(25);
end ;


commit;

select trunc(sysdate+1,'mi') from dual;