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

  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 ;

上一页  [1] [2] 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18