一个通过Oracle8i存储过程返回记录集的程序包(存储过程)

核心提示： -- 打开游标，根据SQL语句获取1级代理商资料，一个通过Oracle8i存储过程返回记录集的程序包(存储过程)(3)，然后根据1级代理商的资料获取2级代理的信息 IF AreaCode!='0' And KeyWord=' ' Then strSQL:='Select A.A

　 -- 打开游标，根据SQL语句获取1级代理商资料，然后根据1级代理商的资料获取2级代理的信息
　 IF AreaCode!='0' And KeyWord=' ' Then
　　 strSQL:='Select A.AgtID,'''',A.Agtcredit,A.AgtProfit,
　　　　　　 B.AreaCode,B.AreaName,0 From Sms_Pay.FstAgtAccount A,Sms_Pay.AreaConfig B
　　　　　　 Where A.IsActive=1 And A.CityCode=B.CityCode And B.AreaCode =''' || AreaCode ||'''';　　　　　
　　　　
　 ElsIf (Trim(AreaCode)!='0' And KeyWord!=' ') Then
　　 strSQL:='Select A.AgtID,'''',A.Agtcredit,A.AgtProfit,
　　　　　　 B.AreaCode,B.AreaName,0 From Sms_Pay.FstAgtAccount A,Sms_Pay.AreaConfig B
　　　　　　 Where A.IsActive=1 And A.CityCode=B.CityCode And B.AreaCode =''' || AreaCode ||
　　　　　　 ''' And (B.AreaName Like ''%' || Trim(KeyWord) || '%'' Or　A.AgtID Like ''%' || Trim(KeyWord) || '%'')';
　 ElsIf (Trim(AreaCode)='0' And KeyWord=' ') Then
　　 strSQL:='Select A.AgtID,'''',A.Agtcredit,A.AgtProfit,
　　　　　　 B.AreaCode,B.AreaName,0 From Sms_Pay.FstAgtAccount A,Sms_Pay.AreaConfig B
　　　　　　 Where A.IsActive=1 And A.CityCode=B.CityCode';　　　　　　　　
　 Else
　　 strSQL:='Select A.AgtID,'''',A.Agtcredit,A.AgtProfit,
　　　　　　 B.AreaCode,B.AreaName,0 From Sms_Pay.FstAgtAccount A,Sms_Pay.AreaConfig B
　　　　　　 Where A.IsActive=1 And A.CityCode=B.CityCode
　　　　　　 And (B.AreaName Like ''%' || KeyWord || '%'' Or　A.AgtID Like ''%' || KeyWord || '%'')';
　 End If;
　
　 Open FstCursor For strSQL;

--检查临时表是否已经存在，如果不存在，则创建，否则插入数据 start

Select Count(*) Into IsExists from all_tables Where Trim(Table_Name)='TEMPAGTBALANCE';
IF IsExists=0 Then
　　 strSQL:='CREATE GLOBAL TEMPORARY TABLE　SMS_PAY.TEMPAgtBalance
　　　 (FSTAGTID VARCHAR2(13) ,SECAgtID　VARCHAR2(13),ParentID　VARCHAR2(13),
　　　 AgtBalance NUMBER(10,2),AgtProfit NUMBER(10,2),AreaCode VARCHAR2(10),
　　　 AreaName VARCHAR2(30),AgtCount NUMBER(10)) on commit preserve rows';
　　 --把临时表的创建选项由on commit delete rows改为on commit preserve rows;　
　　 --否则在调用的时候，回出现ORA-08103: object no longer exists
　　 --DBMS_OUTPUT.PUT_LINE(strSQL);
　　 Execute Immediate strSQL;
　 Else
　　 execute immediate 'Truncate table Sms_pay.TEMPAgtBalance';
　 End IF;

--检查临时表是否已经存在，如果不存在，则创建，否则插入数据 end

Loop
　 Fetch FstCursor into pFSTAGTID,pSECAgtID, pAgtBalance,pAgtProfit,pAreaCode, pAreaName, pAgtCount ;
　 Exit when FstCursor%NOTFOUND;

--1:写1级代理商查询信息

　strSQL:='Insert into TEMPAgtBalance(FSTAGTID,SECAgtID, ParentID,AgtBalance,AgtProfit,AreaCode, AreaName, AgtCount)
　　　　 　 Values(''' || pFSTAGTID || ''',null,null,' || pAgtBalance || ',' || pAgtProfit || ',''' || pAreaCode || ''','''
　　　　 　 || pAreaName || ''',' || pAgtCount || ')';
　　 Execute Immediate strSQL;

--2:写2级代理商查询信息

　strSQL:='Insert into TEMPAgtBalance Select null,AGTID,''' || pFSTAGTID ||''',AGTCREDIT,AGTPROFIT ,''' || pAreaCode || ''',''' || pAreaName || ''',0 From SecAgtAccount Where Trim(BOSSAGTID)=''' || pFSTAGTID || '''';
　 Execute Immediate strSQL;

--3:更新1级代理的下级别代理商数量

　　strSQL:='Select Count(*) From TEMPAgtBalance Where SECAgtID is not null And ParentID=''' || pFSTAGTID || '''';
　　 --DBMS_OUTPUT.PUT_LINE(strSQL);
　　 Execute Immediate strSQL　Into subAgtNumber;
　　 --Select Count(*) Into subAgtNumber From TEMPAgtBalance Where SECAgtID is not null And ParentID=pFSTAGTID;
　　 DBMS_OUTPUT.PUT_LINE(subAgtNumber);
　　 strSQL:='Update TEMPAgtBalance Set AgtCount=' || subAgtNumber || ' Where SECAgtID is null And FSTAGTID=''' || pFSTAGTID || '''';
　　 Execute Immediate strSQL;
　　 --DBMS_OUTPUT.PUT_LINE(strSQL);
　　 --Update TEMPAgtBalance Set AgtCount=subAgtNumber Where SECAgtID is null And FSTAGTID=pFSTAGTID;
End loop;
　　　
Close FstCursor;
Open Re_CURSOR For 'Select * From Sms_pay.TEMPAgtBalance';
ret:=0;
return;
Exception
　 when others then
　 DBMS_OUTPUT.PUT_LINE(SQLERRM);
　 ret:=999;　
　 return;
　 End GetAgtBalance;
　
End ;