This article is recently written in the stored procedure used in a project. Special records for reference! [Color = Blue] Create or Replace PSH_GPRSSTREAMSTAT IS [/ color] [color = red] - Author: administrator - created: 2004-12-8 10:56:01 - purpose: GPRS traffic statistics state - statistics GPRS traffic [/ 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; - defined IP header length 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); - The current GPRS terminal uploaded information length v_upContentLens Number (20, 0); v_uptotallens Number (20, 0); - Accumulated GPRS terminal upload information Content length v_downcontentLens Number (20, 0); V_Downtotallens Number (20, 0); NewID Number (20); Begin - Initialization Select Max (Statid) INTO NEWID from Sh_GPRSSTREAMSTAT; if (newid is null) Then newId: = END IF;
for v_SPINFO In c_SPINFO loop - first acquires SPID - followed by traversing the open c_MDTINFo for select distinct mdtid MDT SPID corresponding to all the current 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; - the following statement is used to select two GPRS terminal to obtain information uploaded flow 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 the following statement is used to obtain the information sent by the service provider traffic 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;
- The statistics stored in the integrated value of the flow state tables 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; - self-increment plus 1 newid: = newid 1; end if; end loop; close c_mdtinfo; commit; begin null END PSH_GPRSSTREAMSTATAT;
