我在函数FT_DEAL_TIME中返回一个管道化表。
但是在这个管道化表函数中需要获取管道化表自身中的最后一行,如果跟当前列表某个字段比较为true则添加到管道化表,否则不添加。
我的做法是:
用max取最后那一条,可是不行报错哎(max那行),管道化表函数不会用,求指导。代码如下: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;
但是在这个管道化表函数中需要获取管道化表自身中的最后一行,如果跟当前列表某个字段比较为true则添加到管道化表,否则不添加。
我的做法是:
用max取最后那一条,可是不行报错哎(max那行),管道化表函数不会用,求指导。代码如下: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;
Time_Data_METADATA的声明部分呢?从代码本身看出来问题,猜测问题出在你的TYPE定义部分。
CREATE OR REPLACE TYPE Time_Data_METADATA_Table
as table of Time_Data_METADATATime_Data_METADATA:
create or replace type Time_Data_METADATA as object
(
dtPOSITIONTIME date,
lLONGITUDE number,
lLATITUDE number,
iVelocity 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;