Oracle存储过程对于Oracle数据库中的重要性毋庸置疑，下面就为您介绍一个Oracle存储过程的使用实例，希望对您学习Oracle存储过程有更多的了解。

最近在写一个项目中所用的Oracle存储过程，由于是***次接触oracle，其间花费了许多功夫，才把功能实现！特记录下来，以供参考.

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 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;