oracle 增量设为3 循环_如何在Oracle中使用游标来实现多重循环？[原创]-数据库专栏,ORACLE...

这篇文章，是最近在写一个项目中所用的存储过程，由于是第一次接触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;