1、根据航路id取所有航路点的函数
create or replace function alinepnts(alid in number)
return tab_airline_pnt is --返回航线的所有航路点组成
srt number(5);
alpnts tab_airline_pnt:=tab_airline_pnt();
dt tab_pnt:= tab_pnt();
dr row_airline_pnt:=row_airline_pnt(0,0,0,0);
oldpid number(12); --上一点id,因为航路点首尾会重复
begin
srt:=0;
oldpid:=0;
for myrow in(
select company_enroute_id, val_sort, en_route_rte_id, start_point_id , end_point_id
from COMPANY_RTE where company_enroute_id = alid
order by val_sort
)loop
dt:=subairway(myrow.start_point_id,myrow.en_route_rte_id,myrow.end_point_id);
-- for v in dt.first..dt.last loop
for v in 1..dt.count() loop
if oldpid <> dt(v).pid then --判断是否跟上点重复
srt:=srt+1;
dr:=row_airline_pnt(alid,myrow.en_route_rte_id,srt,dt(v).pid);
alpnts.extend();
alpnts(alpnts.count()):=dr;
oldpid:=dt(v).pid;
end if;
end loop;
end loop;
return alpnts;
end;
2、根据此函数创建所有航线所有点走向视图,此视图对简单查询也非常缓慢,只有语法研究价值,没有实际价值
create or replace view airline_point as
select t2.alineid, t2.awid, t2.srt, t2.pid
from company_enroute t1,table(alinepnts(t1.company_enroute_id)) t2
where t1.company_enroute_id = t2.alineid;