日期:2014-05-16 浏览次数:20849 次
create or replace function FT_DEAL_TIME(iMucid number) return Time_Data_METADATA_Table PIPELINED is v_time_data_metadata Time_Data_METADATA; nowdate date; predate date; begin for x in (select f.positiontime as positiontime, e.longitude as longitude, e.latitude as latitude, f.velocity as velocity from BMPS_HIS_RECEIVE_GPSSTATUS d, BMPS_HIS_RECEIVE_GPSJPINFO e, BMPS_HIS_RECEIVE_GPSINFO f where d.sequence = f.sequence and e.sequence = f.sequence and d.status14 = 1 and d.mcuid = iMucid order by d.positiontime asc) loop nowdate := x.positiontime; predate := select max(t.positiontime) from table(FT_DEAL_TIME(iMucid)) t;; if (nowdate - predate) * 24 * 60 > 5 then v_time_data_metadata := Time_Data_METADATA(x.positiontime, x.longitude, x.latitude, x.velocity); pipe row(v_time_data_metadata); end if; end loop; return; end;
create or replace function FT_DEAL_TIME(iMucid number) return Time_Data_METADATA_Table PIPELINED is v_time_data_metadata Time_Data_METADATA; nowdate date; predate date; begin for x in (select f.positiontime as positiontime, e.longitude as longitude, e.latitude as latitude, f.velocity as velocity from BMPS_HIS_RECEIVE_GPSSTATUS d, BMPS_HIS_RECEIVE_GPSJPINFO e, BMPS_HIS_RECEIVE_GPSINFO f where d.sequence = f.sequence and e.sequence = f.sequence and d.status14 = 1 and d.mcuid = iMucid order by d.positiontime asc) loop nowdate := x.positiontime; --predate := select max(t.positiontime) from table(FT_DEAL_TIME(iMucid)) t; -- 下面的判断条件得加上第一条记录时predate is null的判断 if predate is null or (nowdate - predate) * 24 * 60 > 5 then v_time_data_metadata := Time_Data_METADATA(x.positiontime, x.longitude, x.latitude, x.velocity); pipe row(v_time_data_metadata); -- 记录前一条记录的时间 predate := nowdate(); end if; end loop; return; end;
------解决方案--------------------
跟踪维护最大值就行了.
if predate is null or predate < nowdate then
predate := nowdate;
end if;