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;