存储过程自动执行_存储过程立即执行-优快云博客

本文链接：https://blog.youkuaiyun.com/robinpipi/article/details/6603186

本文介绍了一个用于每天更新用户数量的程序，通过SQL查询获取前一天的用户数据，并根据查询结果进行插入或删除操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace
procedure   insertUserNums   as 
icount number;
    begin 
   select nvl(count(*),0) into icount from userchart where everydate=to_char(sysdate-1,'yyyy-mm-dd');
    if icount=0 then 
    insert   into   userchart   values(to_char(sysdate-1,'yyyy-mm-dd'),(select nvl(max(num),0) from usercharteveryday where trunc(everydaydate)=trunc(sysdate-1))); 
    else 
    delete from userchart where everydate=to_char(sysdate-1,'yyyy-mm-dd');
     insert   into   userchart   values(to_char(sysdate-1,'yyyy-mm-dd'),(select nvl(max(num),0) from usercharteveryday where trunc(everydaydate)=trunc(sysdate-1))); 
   
  end if;
  end;  
  
--variable job1 number;
--  begin 
--  dbms_job.submit(:job1,'insertUserNums;',sysdate,'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)');--每晚1点半执行。
-- end; 
 
-- begin 
--dbms_job.run(:job1); 
-- end; 
 
variable job_insertUserNums number; 
 begin 
    sys.dbms_job.submit(
            job =>:job_insertUserNums, 
             what   =>'insertUserNums;', 
             next_date   => to_date('15-03-2011 01:00:00','dd-mm-yyyy hh24:mi:ss'), 
             interval   => 'trunc(SYSDATE+1)'); 
    commit; 
end; 

 begin 
dbms_job.run(:job_insertUserNums); 
 end;