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

欢迎大家阅读《如何在Oracle中使用游标来实现多重循环_oracle》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)