如何在Oracle中使用游标来实现多重循环？[原创]
2024-08-29 13:29:37
供稿：网友
这篇文章，是最近在写一个项目中所用的存储过程，由于是第一次接触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;
菜鸟学堂：
