create or replace procedure test
as
v_mintime date;
begin
select
finishtime into v_mintime
from
(select finishtime from trafficflow order by finishtime) t1
where rownum <=1;
while (sysdate-v_mintime)*24*60>15 loop
INSERT INTO Tflux
select crossingid,getdate() as collecttime,sum(volume1) as wind1,sum(volume2) as wind2,sum(volume3) as wind3,sum(volume4) as wind4,
sum(volume5) as wind5, sum(volume6) as wind6 ,sum(volume7) as wind7 ,sum(volume8) as wind8,
sum(volume9) as wind9, sum(volume10) as wind10,sum(volume11) as wind11,sum(volume12) as wind12,
sum(volume13) as wind13,sum(volume14) as wind14,sum(volume15) as wind15,sum(volume16) as wind16,
sum(volume17) as wind17,sum(volume18) as wind18,sum(volume19) as wind19,sum(volume20) as wind20,
sum(volume21) as wind21,sum(volume22) as wind22,sum(volume23) as wind23,sum(volume24) as wind24,
sum(volume25) as wind25,sum(volume26) as wind26,sum(volume27) as wind27,sum(volume28) as wind28,
sum(volume29) as wind29,sum(volume30) as wind30,sum(volume31) as wind31,sum(volume32) as wind32
from trafficflow
where finishtime between v_mintime and v_mintime*24*60+4
group by crossingid;
delete from trafficflow
where finishtime between v_mintime and v_mintime*24*60+4; UPDATE Tflux
SET collecttime = v_mintime
WHERE (sysdate-collecttime)*24*60<1;
v_mintime:=v_mintime*24*60 + 5;
end loop;
end;
as
v_mintime date;
begin
select
finishtime into v_mintime
from
(select finishtime from trafficflow order by finishtime) t1
where rownum <=1;
while (sysdate-v_mintime)*24*60>15 loop
INSERT INTO Tflux
select crossingid,getdate() as collecttime,sum(volume1) as wind1,sum(volume2) as wind2,sum(volume3) as wind3,sum(volume4) as wind4,
sum(volume5) as wind5, sum(volume6) as wind6 ,sum(volume7) as wind7 ,sum(volume8) as wind8,
sum(volume9) as wind9, sum(volume10) as wind10,sum(volume11) as wind11,sum(volume12) as wind12,
sum(volume13) as wind13,sum(volume14) as wind14,sum(volume15) as wind15,sum(volume16) as wind16,
sum(volume17) as wind17,sum(volume18) as wind18,sum(volume19) as wind19,sum(volume20) as wind20,
sum(volume21) as wind21,sum(volume22) as wind22,sum(volume23) as wind23,sum(volume24) as wind24,
sum(volume25) as wind25,sum(volume26) as wind26,sum(volume27) as wind27,sum(volume28) as wind28,
sum(volume29) as wind29,sum(volume30) as wind30,sum(volume31) as wind31,sum(volume32) as wind32
from trafficflow
where finishtime between v_mintime and v_mintime*24*60+4
group by crossingid;
delete from trafficflow
where finishtime between v_mintime and v_mintime*24*60+4; UPDATE Tflux
SET collecttime = v_mintime
WHERE (sysdate-collecttime)*24*60<1;
v_mintime:=v_mintime*24*60 + 5;
end loop;
end;
我怎么没有见过这样的语法,这句对吗?
上面的语句编译有错误
from trafficflow
where finishtime between v_mintime and v_mintime*24*60+4
group by crossingid;finishtime,vmintime是什么类型的。其中where finishtime between v_mintime and v_mintime*24*60+4
可能有错
dbms_job.submit(:job_autoCreateTable,'CAutoCreateSjczrzb;',sysdate,'sysdate+1/24');
dbms_job.run(:job_autoCreateTable);
end;
/--删除JOB
--begin
--dbms_job.remove(:job_autoCreateTable);
--end;
--/
--SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM user_JOBS;
1、DATEADD(minute, 4, @mintime) == v_mintime*24*60+4
你的sqlserver中就写了这些呀!DATEADD(minute, 4, @mintime)你要做什么比较?2、(sysdate-v_mintime)*24*60 ==DATEDIFF(minute, @mintime, getdate())
(sysdate-v_mintime)得到当前时间和变量时间的天差值,*24*60就换算成分钟了!