这篇文章,是最近在写一个项目中所用的存储过程,由于是第一次接触oracle,其间花费了许多功夫,才把功能实现!特记录下来,以供参考!
[color=blue]create or replace package psh_gprsstreamstat is[/color]
[color=red]
— author : administrator
— created : 2004-12-8 10:56:01
— purpose : gprs流量统计状态
— 统计gprs流量
[/color]
type
c_cur is ref cursor;
function calcu_gprsstream return number;
end psh_gprsstreamstat;
—————————————————————————–
create or replace package body psh_gprsstreamstat is
function calcu_gprsstream return number
is
c_ippackheadlen constant number := 40; — 定义ip包头长度
cursor c_spinfo is
select distinct spid from sh_spinfo where isactive = 0;
c_mdtinfo c_cur ;
v_mdtinfo number;
v_uptranscontentlens number(20,0); — 存放当前gprs终端上传转发的信息内容长度
v_upcontentlens number(20,0);
v_uptotallens number(20,0); — 累计gprs终端上传的信息内容长度
v_downcontentlens number(20,0);
v_downtotallens number(20,0);
newid number(20,0);
begin
— 初始化
select max(statid) into newid from sh_gprsstreamstat;
if (newid is null) then
newid := 1;
end if;
for v_spinfo in c_spinfo loop — 首先获取spid
— 其次遍历出与当前spid对应的所有mdt
open c_mdtinfo for select distinct mdtid from sh_mdtinfo where (isactive = 0) and (spid = v_spinfo.spid);
loop
fetch c_mdtinfo into v_mdtinfo;
exit when c_mdtinfo%notfound;
v_upcontentlens := 0;
v_uptranscontentlens := 0;
v_uptotallens := 0;
v_downcontentlens := 0;
v_downtotallens := 0;
— 下面两个select语句是用来获得gprs终端上传的信息流量
select sum(length(content) + c_ippackheadlen) into v_upcontentlens from sh_gprsmdtupinfo where (mdtid = v_mdtinfo) and (spid = v_spinfo.spid) ;
select sum(length(content) + c_ippackheadlen) into v_uptranscontentlens from sh_gprsmdttransinfo where (issuccess = 1) and (mdtid = v_mdtinfo) and (spid = v_spinfo.spid) ;
if (v_upcontentlens is null) then
v_upcontentlens := 0;
end if;
if (v_uptranscontentlens is null) then
v_uptranscontentlens := 0;
end if;
v_uptotallens := v_uptotallens + v_upcontentlens + v_uptranscontentlens;
— 下面的select语句是用来获得服务商下发的信息流量
select sum(length(content) + c_ippackheadlen) into v_downcontentlens from sh_gprsspdowninfo where (mdtid = v_mdtinfo) and (spid = v_spinfo.spid) ;
if (v_downcontentlens is null) then
v_downcontentlens := 0;
end if;
v_downtotallens := v_downtotallens + v_downcontentlens ;
— 将统计出的累计值存放到流量状态统计表中
if (v_uptotallens >0) or (v_downtotallens > 0) then
insert into sh_gprsstreamstat (statid,spid,mdtid,starttime,endtime,mdtupstream,spdownstream)
values(newid,v_spinfo.spid,v_mdtinfo,sysdate,sysdate,v_uptotallens,v_downtotallens);
— 自增量加1
newid := newid + 1;
end if;
end loop;
close c_mdtinfo;
commit;
end loop;
return 1;
end;
begin
null;
end psh_gprsstreamstat;