如何在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;
菜鳥學堂：
