首页 > 代码库 > 2015.1.15 利用Oracle函数返回表结果 重大技术进步！
2015.1.15 利用Oracle函数返回表结果 重大技术进步！
-- sql 调用 select * from table( get_airway_subpoint(x,x,x))
/////////////////////////////////////////////////////////////////////
方法一、用游标实现，孙立明提供，没有后面的方案二好，可以不用游标，更简单的方案2
//////////////////////////////////////////////////////////////////////
create or replace function subairway(pid1 in number,
awid in number,
pid2 in number) return tab_pnt is
--函数功能是返回awid航路两点间的所有航路点
type mycursor is ref cursor; --定义游标类型
dr row_pnt := row_pnt(0, 0, null); --type 行类型每条点记录 create or replace type row_pnt is object(srt number(5),pid number(11),pnm varchar2(50))
dt tab_pnt:= tab_pnt(); --type 表类型，返回航路所有点 create or replace type tab_pnt as table of row_pnt
dtt tab_pnt:= tab_pnt(); --结果表，两点之间的所有表记录
-- 表类型和行类型必须先初始化，否则编译不错但运行会提示错误
cur_pnt mycursor; -- 游标
n1 number(5); --两点在航路走向中的序号
n2 number (5);
tm number(5);
begin
open cur_pnt for --游标可看做一张结果表
select st,pid,pnm from (
select f1.code_sort st,f2.airway_point1 pid,f3.txt_name || decode(f3.tab, ‘DES‘, ‘‘, f3.tab) pnm
from rte_seg f1, segment f2, airway_point f3
where f1.segment_id = f2.segment_id
and f2.airway_point1 = f3.significant_point_id
and f1.en_route_rte_id = awid
union
select f1.code_sort + 1 st, f2.airway_point2 pid,f3.txt_name || decode(f3.tab, ‘DES‘, ‘‘, f3.tab) pnm
from rte_seg f1, segment f2, airway_point f3
where f1.segment_id = f2.segment_id
and f2.airway_point2 = f3.significant_point_id
and f1.code_sort =(select max(code_sort)from rte_seg where rte_seg.en_route_rte_id = f1.en_route_rte_id)
and f1.en_route_rte_id = awid
order by st
);
IF cur_pnt%NOTFOUND THEN --如果游标内没数据，返回空表
RETURN dt;
END IF;
loop
FETCH cur_pnt
INTO dr.srt, dr.pid, dr.pnm;
EXIT WHEN cur_pnt%NOTFOUND; --到达游标结束，这行位置不能放最后，否则会重复一行
dt.extend(); --dt增加一行新行
dt(dt.count()) := dr; --dt最后一行=dr
END LOOP;
if pid1=0 or pid2=0 then --如果输入变量首点id或末点id 为0 表示返回所有航路走向
return dt;
end if;
n1:=-1; --初始值，用于判断时候再航路上找到输入点
n2:=-1;
for v in 1..dt.count() loop --从第一行到dt表的最后一行
if pid1 = dt(v).pid then n1:=dt(v).srt; --找到n1 n2对应位置
elsif pid2 = dt(v).pid then n2:=dt(v).srt;
end if;
end loop;
if n1=-1 or n2=-1 then --至少有一点没匹配上，返回空表
return dtt;
end if;
if n1<n2 then --点1序号小于点2序号，正常循环
for v in n1..n2 loop
if dt(v).srt >= n1 and dt(v).srt<=n2 then --dt(v).srt 从表结构逐级访问行结构、字段结构
dtt.extend();
dtt(dtt.count()):= dt(v);
end if;
end loop;
end if;
if n1>n2 then --如果n1>n2说明p1,p2顺序颠倒
tm:=n1;
while tm >= n2 LOOP
begin
dtt.extend();
dtt(dtt.count()):= dt(tm);
tm:= tm - 1;
end;
end LOOP;
end if;
return dtt;
end;
////////////////////////////////////////////////////////////////
方案2 不用游标 关键是 for myrow in (select ...) loop 函数 其余与方案一相同
////////////////////////////////////////////////////////////////
create or replace function subairway(pid1 in number,
awid in number,
pid2 in number,
ishis in number default 0 ) return tab_pnt is
--函数功能是返回awid航路两点间的所有航路点，使用此函数获得某航路两点间的所有点后时，不能用srt排序，否则永远只能得到按原航路顺序的点串，而不是按入点到出点顺序的点串。
dr row_pnt := row_pnt(0, 0, null); --type 行类型 每条点记录 create or replace type row_pnt is object(srt number(5),pid number(11),pnm varchar2(50))
dt tab_pnt:= tab_pnt(); --type 表类型，返回航路所有点 create or replace type tab_pnt as table of row_pnt
dtt tab_pnt:= tab_pnt(); --结果表，两点之间的所有表记录
-- 表类型和行类型必须先初始化，否则编译不错但运行会提示错误
n1 number(5); --两点在航路走向中的序号
n2 number (5);
tm number(5);
begin
if ishis=1 then --2016.9.7 原来写的临时航路居然找不到了，重写
for myrow in
( select sort st, airway_point_id pid, airway_point_name pnm from his_airway_pnts
where airway_id=awid
order by sort
)
loop
dr:=row_pnt(myrow.st,myrow.pid,myrow.pnm);
dt.extend(); --dt增加一行新行
dt(dt.count()) := dr; --dt最后一行=dr
END LOOP;
else
for myrow in
(
select st,pid,pnm from (
select f1.code_sort st,f2.airway_point1 pid,f3.txt_name || decode(f3.tab, ‘DES‘, ‘‘, f3.tab) pnm
from rte_seg f1, segment f2, airway_point f3
where f1.segment_id = f2.segment_id
and f2.airway_point1 = f3.significant_point_id
and f1.en_route_rte_id = awid
union
select f1.code_sort + 1 st, f2.airway_point2 pid,f3.txt_name || decode(f3.tab, ‘DES‘, ‘‘, f3.tab) pnm
from rte_seg f1, segment f2, airway_point f3
where f1.segment_id = f2.segment_id
and f2.airway_point2 = f3.significant_point_id
and f1.code_sort =(select max(code_sort)from rte_seg where rte_seg.en_route_rte_id = f1.en_route_rte_id)
and f1.en_route_rte_id = awid
order by st
)
)
loop
dr:=row_pnt(myrow.st,myrow.pid,myrow.pnm);
dt.extend(); --dt增加一行新行
dt(dt.count()) := dr; --dt最后一行=dr
END LOOP;
end if;
if pid1=0 or pid2=0 then --如果输入变量 首点id或末点id 为0 表示返回所有航路走向
return dt;
end if;
n1:=-1; --初始值，用于判断时候再航路上找到输入点
n2:=-1;
for v in 1..dt.count() loop --从第一行到dt表的最后一行
if pid1 = dt(v).pid then n1:=dt(v).srt; --找到n1 n2对应位置
elsif pid2 = dt(v).pid then n2:=dt(v).srt;
end if;
end loop;
if n1=-1 or n2=-1 then --至少有一点没匹配上，返回空表
return dtt;
end if;
if n1<n2 then --点1序号小于点2序号，正常循环
for v in n1..n2 loop
if dt(v).srt >= n1 and dt(v).srt<=n2 then --dt(v).srt 从表结构逐级访问行结构、字段结构
dtt.extend();
dtt(dtt.count()):= dt(v);
end if;
end loop;
end if;
if n1>n2 then --如果n1>n2说明p1,p2顺序颠倒
tm:=n1;
while tm >= n2 LOOP
begin
dtt.extend();
dtt(dtt.count()):= dt(tm);
dtt(dtt.count()).srt:=n1-tm+1; --2016.9.7 倒序排列时，为了让序号体现真正的从小到大顺序，改写每点序号
tm:= tm - 1;
end;
end LOOP;
end if;
return dtt;
end;
2015.1.15 利用Oracle函数返回表结果 重大技术进步！