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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10294527/viewspace-122244/，如需转载，请注明出处，否则将追究法律责任。