关于oracle的job

variable job number;

begin
dbms_job.submit(:job,'proc_updaterelate;',trunc(sysdate),'trunc(sysdate+1)');
end;

begin
dbms_job.submit(:job,'proc_updaterelate;',sysdate,'sysdate+5/1440');
end;

 

 

begin
dbms_job.run(42);
end;

begin
dbms_job.run(:job1);
end;

begin
dbms_job.remove(41);
end;

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
FROM DBA_JOBS;

 

select * from tbl_process_draft mm where mm.endtime is not null and  mm.draftid=
(
 select nn.draftid from tbl_related_proc nn where nn.draftid=mm.draftid
 and nn.status=1 and nn.userid='yefei'
 and rownum=1
)

 

select   distinct hh.draftid from
(select hh.draftid from tbl_related_proc hh where hh.status=1  ) hh
inner join
(select mm.draftid from tbl_process_draft mm where mm.endtime is not null )
mm on mm.draftid=hh.draftid

 

create or replace procedure proc_updaterelate

as

v_draftid tbl_related_proc.draftid%TYPE;
cursor cur_purch is
/*select xx.vv from (
select (select mm.draftid from tbl_related_proc hh where hh.status=1
and  hh.draftid=mm.draftid and hh.userid='yefei' and rownum=1) vv
from tbl_process_draft mm where  mm.endtime is not null
) xx where xx.vv is not null;*/

select   distinct hh.draftid  from
(select hh.draftid from tbl_related_proc hh where hh.status=1  ) hh
inner join
(select mm.draftid from tbl_process_draft mm where mm.endtime is not null )
mm on mm.draftid=hh.draftid;

 
begin

 
 
  open cur_purch;
    loop
      fetch cur_purch into v_draftid;
       exit when cur_purch%notfound;
      update tbl_related_proc hh set hh.status=0
      where hh.draftid=v_draftid;
    end loop;
   
 close cur_purch;
 commit;
 
exception
  when others then
    rollback;
 
 
end proc_updaterelate;