create or replace package PSH_GPRSSTREAMSTAT is -- Author : ADMINISTRATOR -- Created : 2004-12-8 10:56:01 -- Purpose : GPRS流量统计状态 -- 统计GPRS流量 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 0select 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 -- 初始化 0select 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 0select 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. -- 下面两个0select语句是用来获得GPRS终端上传的信息流量 0select sum(length (content) c_IPPackHeadLen) into v_UpContentLens from sh_gprsmdtupinfo where (MDTID = v_MDTINFO) and (spid = v_SPINFO.spid) . 0select 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语句是用来获得服务商下发的信息流量 0select 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.