How do I use cursors in Oracle to implement multiple loops? Original

oracle| Cycle | cursors | original
This article, is recently in the writing of a project used in the stored procedures, because it is the first contact with Oracle, it took a lot of effort to achieve the function! Special records, for reference!
Create or Replace package Psh_gprsstreamstat is

--Author:administrator
--Created:2004-12-8 10:56:01
--Purpose:gprs flow statistics State
--Statistic GPRS traffic

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; --Define IP header length
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); --Storing the information content length of the current GPRS terminal uploading and forwarding
V_upcontentlens number (20,0);
V_uptotallens number (20,0); --Cumulative GPRS terminal upload information content length
V_downcontentlens number (20,0);
V_downtotallens number (20,0);
NewID number (20,0);

Begin
--Initialization
Select Max (Statid) into the NewID from Sh_gprsstreamstat;
if (NewID is null) then
NewID: = 1;
End If;

For V_spinfo in C_spinfo Loop--Get the SPID first
--second traversal of all MDT corresponding to the current SPID
The open c_mdtinfo for select distinct Mdtid the 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;
--The following two SELECT statements are used to obtain the information flow of GPRS terminal uploads
Select sum (length (content) + C_ippackheadlen) into the v_upcontentlens from Sh_gprsmdtupinfo where (Mdtid = V_mdtinfo) and (sp id = v_spinfo.spid);
Select sum (length (content) + C_ippackheadlen) into the v_uptranscontentlens from Sh_gprsmdttransinfo where (issuccess = ' 1 ') a nd (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;

--The following SELECT statement is used to obtain information flow from the service provider
Select sum (length (content) + C_ippackheadlen) into the 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;

--to store the cumulative value in the Flow status statistics
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);
--Self increment plus 1
NewID: = NewID + 1;
End If;
End Loop;
Close C_mdtinfo;
Commit
End Loop;
return 1;
End

Begin
Null
End Psh_gprsstreamstat;