定时器存储过程

--创建表
create table test(id int,starttime date,state int);
--创建存储过程
create or replace procedure sp_update is
cursor cur is select * from test where state=0;
v_id int;
v_starttime date;
v_state int;
begin
   open cur;
      loop
        fetch cur into v_id,v_starttime,v_state;
           exit when cur%notfound;
              update test set state=1 where starttime=sysdate;
              commit;
           end loop;
      close cur;
   end;
--创建job
variable job number;
begin
  dbms_job.submit(:job,'sp_update;',sysdate,'sysdate+1/1440');  --每天1440分钟，即每分钟执行一次
end;

--每月一号执行
variable job number;
begin
  dbms_job.submit(:job,'sp_update;',sysdate,'trunc(sysdate)+1+19/24');  --每天19点执行
end;




create table myself(a varchar2(10));
create or replace procedure sp_insertM as
begin
  insert into myself values('a');
end;

variable myt number;
begin
   dbms_job.submit(:myt,'sp_insertM;',sysdate,'sysdate+1/1440');
end;