下面是存储过程的一部分
Begin
--在网用户计费
Begin
For CurrentPart In Cu_BY_USER_NOW Loop --loop1
l_sTrueTel := CurrentPart.TrueTel;
l_sVirtualTel := CurrentPart.VirtualTel;
l_sTrueTel := Trim(l_sTrueTel);
l_sVirtualTel := Trim(l_sVirtualTel);
If (lengthb(l_sTrueTel)>0 And lengthb(l_sVirtualTel)>0) Then
--该真实号码本月已对该虚拟号码计费
l_iExist := 0;
Begin
Select l_iExist = Count(*)
From T_SGIP_MS
Where ChargeNumber=l_sTrueTel
And FeeMonth=to_char(Sysdate,'YYYYMM')
And VirtualTel=l_sVirtualTel;
Exception
When Others Then
l_iExist := 0;
End;
--只有该真实号码本月没有对该虚拟号码计费才计费
If l_iExist=0 Then
--本月用户已计包月费个数
l_iCount := 0;
Begin
Select l_iCount = Count(*)
From T_SGIP_MS
Where ChargeNumber=l_sTrueTel
And FeeMonth=to_char(Sysdate,'YYYYMM');
Exception
When Others Then
l_iCount := 0;
End;
--设定业务代码
if l_iCount = 0 Then
l_sServiceType := 'CHBY15';
elsif l_iCount = 1 Then
l_sServiceType := 'CHBYX1';
elsif l_iCount = 2 Then
l_sServiceType := 'CHBYX2';
elsif l_iCount = 3 Then
l_sServiceType := 'CHBYX3';
elsif l_iCount = 4 Then
l_sServiceType := 'CHBYX4';
elsif l_iCount = 5 Then
l_sServiceType := 'CHBYX5';
elsif l_iCount = 6 Then
l_sServiceType := 'CHBYX6';
elsif l_iCount = 7 Then
l_sServiceType := 'CHBYX7';
elsif l_iCount = 8 Then
l_sServiceType := 'CHBYX8';
elsif l_iCount = 9 Then
l_sServiceType := 'CHBYX9';
else
Null;
end If;
--一个月内最多允许计费10次
If l_iCount < 10 Then
Begin
Insert Into t_sgip_ms(OpType,OpUser,Seq,SPNumber,UserNumber,ChargeNumber,MessageContent,CorpId,ServiceType, FeeType,FeeValue,GivenValue,AgentFlag,MOrelatetoMTFlag,Priority,ReportFlag,FeeMonth,VirtualTel)
Values(0,'job',s_sgip_ms.Nextval,'8417', l_sTrueTel,l_sTrueTel,'包月计费：' || l_sVirtualTel,'03713',l_sServiceType, 3,'1500','0',0,3,9,1,TO_CHAR(Sysdate,'YYYYMM'),l_sVirtualTel);
Commit;
Exception
When Others Then
Rollback;
End;
End If;
End If;
End If;
End Loop;
Exception
When Others Then
Rollback;
End;
