如何在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;