2012-06-25创建数据库函数的基本语法
create or replace function minday
(sid varchar2, npr number) //参数不能具体写变量类型，如number(5,2)
return number is md number(5,2); //返回结果要明确类型和精度
//定义数组型变量类型，因为select into 后只能跟一个变量（确认？），因此需select出多个列时，只能预先定义数组型变量。
type m9s_t is record(m1 number(5,2),m2 number(5,2),m3 number(5,2),m4 number(5,2),m5 number(5,2),m6 number(5,2),m7 number(5,2),m8 number(5,2),m9 number(5,2) );
//定义类型的实例
m9s m9s_t;
begin
md:=0;
select mn1,mn2,mn3,mn4,mn5,mn6,mn7,mn8,mn9 into m9s from jb where id=sid; //sid变量名不能与字段名重复
if npr<=m9s.m9 and m9s.m9>0 then md:=9;
elsif npr<=m9s.m8 and m9s.m8>0 then md:=8; //注意elsif 少一个e
elsif npr<=m9s.m7 and m9s.m7>0 then md:=7;
elsif npr<=m9s.m6 and m9s.m6>0 then md:=6;
elsif npr<=m9s.m5 and m9s.m5>0 then md:=5;
elsif npr<=m9s.m4 and m9s.m4>0 then md:=4;
elsif npr<=m9s.m3 and m9s.m3>0 then md:=3;
elsif npr<=m9s.m2 and m9s.m2>0 then md:=2;
elsif npr<=m9s.m1 and m9s.m1>0 then md:=1;
end if;
return md;
end;
create or replace function mb40 (sid varchar2,pr number) return number is nm number(5,2);
mpr number(5,2);
begin
mpr:=0;
select max(pr) into mpr from DY where id =sid and rownum<40 order by dt desc;
if pr=0 or mpr=0 then return 0;
else
nm:=100*(pr-mpr)/mpr;
nm:=round(nm,2);
return nm;
end if;
end;
//////////////////////////
create or replace function bl (n1 number,n2 number,w number)
return number is nm number(5,2);
begin
if n2=0 then return 0;
else
nm:=100*(n1-n2)/n2;
nm:=round(nm,w);
return nm;
end if;
end;
以下4函数来自临时航线
1、/////////////////////////////////////////////////////
create or replace function airlinemingao(aid in number) return number is
--函数功能是返回航线aid最低高度层
restStr varchar2(200); --该航线所有高度层组合在一起字符串
splitchar varchar2(5); --分隔符
minval number(5); --最小高度值
thisStr varchar2(18);--取得的当前被分割出的字符串
indexStr int;--临时存放分隔符在字符串中的位置
tmp number(5);
begin
splitchar:='/';
minval:=125; --默认最小高度值时12500米
select levels ||'/'|| level_chg_1 ||'/'|| level_chg_2||'/'|| level_chg_3||'/'|| level_chg_4||'/'|| level_chg_5 into restStr
from COMPANY_ENROUTE where company_enroute_id =aid;
while length(restStr) != 0
LOOP
<<top>>
indexStr := instr(restStr,splitchar); --从子串中取分隔符的第一个位置
if indexStr = 0 and length(restStr) != 0 then--在剩余的串中找不到分隔符
thisStr := restStr; --取得当前的字符串
thisStr:=REGEXP_REPLACE(thisStr,'[^0-9]');--用正则表达式去除当前字符串中的非数字字符
if length(thisStr)=0 then
thisStr:='125';
end if;
tmp:=to_number(thisStr); --将当前字符串转化为数字
if tmp < minval then --比较得到最小值
minval:=tmp;
end if;
return minval;
end if;
if indexStr = 1 then---第一个字符便为分隔符,此时去掉分隔符
restStr := substr(restStr,2);
goto top;
end if;
if length(restStr) = 0 or restStr is null then
return minval;
end if;
thisStr := substr(restStr,1,indexStr - 1); --取得当前的字符串
restStr := substr(restStr,indexStr + 1);---取剩余的字符串
thisStr:=REGEXP_REPLACE(thisStr,'[^0-9]'); --用正则表达式去除当前字符串中的非数字字符
if length(thisStr)=0 then
thisStr:='125';
end if;
tmp:=to_number(thisStr); --将当前字符串转化为数字
if tmp < minval then --比较得到最小值
minval:=tmp;
end if;
END LOOP;
return minval;
end;
2、//////////////////////////////////////////////////
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;
3、/////////////////////////////////////////////////////////
create or replace function alinezx(alid in number)
return tab_airline_zx is --返回航线的所有航路、航路点组成，对航路出入点，重复该点，但航路不重复,该点在该航路中的作用
srt number(5);
alpnts tab_airline_zx:=tab_airline_zx();
dt tab_pnt:= tab_pnt();
dr row_airline_zx:=row_airline_zx(0,0,0,0,0);
ptype number(5); --点的作用
begin
srt:=0;
ptype:=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 1..dt.count() loop
srt:=srt+1;
if v=1 then ptype:=1; --当该点是航路第一点时，标示为航路入点
elsif v=dt.count() then ptype:=2; --当该点是航路最后一点时，标示为航路出点
else ptype:=0; --其它情况为普通点
end if;
dr:=row_airline_zx(alid,myrow.en_route_rte_id,srt,dt(v).pid,ptype);
alpnts.extend();
alpnts(alpnts.count()):=dr;
end loop;
end loop;
return alpnts;
end;
4、//////////////////////////////////////////////
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;
