首页 > 代码库 > 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函数返回表结果 重大技术进步!