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

提示:在Oracle8i中,如果需要通过存储过程返回结果集, 需要使用游标!

CREATE OR REPLACE  PACKAGE BODY "SMS_PAY"."SMSMAINT"                                                                              
IS
 
 --功能描述：查询代理银行交易流水信息 writer: wang haibo 2004-08-24
 PROCEDURE GetAgtBankFlow(AreaCode in varchar2,KeyWord in varchar2,startTradeDate in varchar2,endTradeDate in varchar2,Re_CURSOR OUT T_CURSOR,ret out number)
   is
   
  IsExists number;
  
  strSQL VARCHAR2(2048);
  

 Begin

  --检查临时表是否已经存在，如果不存在，则创建，否则插入数据 start
 Select Count(*) Into IsExists from all_tables Where Table_Name=@#TEMPAGTBANKFLOW@#;
 
       
 IF IsExists=0 Then
 
    strSQL:=@#CREATE GLOBAL TEMPORARY TABLE  SMS_PAY.TEMPAgtBankFlow
      (AGTID VARCHAR2(13) ,BANK_GLIDE Number(12),TradeType VARCHAR2(20),Trade_Money Number(14,2),
      TRADEDATE VARCHAR2(10),TRADETIME VARCHAR2(8),BankNAME VARCHAR2(30),
      AreaCode Varchar2(30), AreaName VARCHAR2(30),State VARCHAR2(30),CheckFlag Number(1),
      StatusFlag Number(1),Suclearcase/" target="_blank" >ccFlag Number(1)) on commit preserve rows@#;

     --把临时表的创建选项由on commit delete rows改为on commit preserve rows; 
     --否则在调用的时候，回出现ORA-08103: object no longer exists
    
     Execute Immediate strSQL;
    
  Else
    
     Execute immediate @#Truncate table Sms_pay.TEMPAGTBANKFLOW@#;
    
  End IF;
  --检查临时表是否已经存在，如果不存在，则创建，否则插入数据 end
   
  ---功能：把满足条件的一级代理商转帐信息导入数据库 Start
 
  /*strSQL:=@#Insert Into TEMPAgtBankFlow
           Select A.Agent_Mobile,A.Bank_Glide,
            Decode(A.OPTCode,@#@#100@#@#,@#@#交款交易@#@#,@#@#101@#@#,@#@#交款冲正@#@#,@#@#900@#@#,@#@#抹帐交易@#@#,@#@#类型不明@#@#) as TradeType,A.Trade_Money,
            TO_Char(To_Date(A.Trade_Date,@#@#YYYYMMDD@#@#),@#@#YYYY-MM-DD@#@#) as TradeDate,
            TO_Char(To_Date(A.Trade_Time,@#@#hh24miss@#@#),@#@#hh24:mi:ss@#@#) as TradeTime,B.BankName,C.AreaCode,C.AreaName,
            (Case When CheckFlag=-1 Then @#@#/Images/state_rollback.gif@#@# When SuccFlag=-1 Then @#@#/Images/state_cancel.gif@#@# When StatusFlag=-1 Then @#@#/Images/state_unnormal.gif@#@# Else @#@#/Images/state_normal.gif@#@# End) as State
           From Bank_Stream_Account A,BankInfoConfig B,AreaConfig C,FstAgtAccount D
            Where A.Bank_ID=B.BankCode And Trim(A.Agent_Mobile)=D.AgtID And C.CityCode=D.CityCode @#;
                   
  IF AreaCode!=@#0@# Then   --条件1： 选择所有地区的信息    
     strSQL:=strSQL || @# And Trim(C.AreaCode)=@#@#@# || AreaCode || @#@#@# @#;
  End IF;  
    
  IF TradeDate!=@# @# Then  --条件2： 选择所有地区的信息,选择所有交易日期的信息

     strSQL:=strSQL || @# And TO_Char(To_Date(A.Trade_Date,@#@#YYYYMMDD@#@#),@#@#YYYY-MM-DD@#@#)=@#@#@# || TradeDate || @#@#@# @#;       
          
  End If;
 
  IF KeyWord!=@# @# Then  --条件3： 选择所有地区的信息,选择所有交易日期的信息,选择和查询关键字匹配的信息
    
     strSQL:=strSQL || @#  And (D.AgtID Like@#@#%@# || KeyWord || @#%@#@# Or TO_Char(A.BANK_GLIDE) Like @#@#%@# || KeyWord || @#%@#@# Or B.BankName Like @#@#%@# || KeyWord ||@#%@#@#) @#;
 
  End If; 
 
  Execute Immediate strSQL;    
 
  ---功能：把满足条件的一级代理商转帐信息导入数据库 End
  */
 
  ---功能：把满足条件的二级代理商转帐信息导入数据库 Start
   strSQL:=@#Insert Into TEMPAgtBankFlow
                 Select A.Agent_Mobile,A.Bank_Glide,
                   Decode(A.OPTCode,@#@#100@#@#,@#@#交款交易@#@#,@#@#101@#@#,@#@#交款冲正@#@#,@#@#900@#@#,@#@#抹帐交易@#@#,@#@#类型不明@#@#) as TradeType,A.Trade_Money,
                   TO_Char(To_Date(A.Trade_Date,@#@#YYYYMMDD@#@#),@#@#YYYY-MM-DD@#@#) as TradeDate,
                   TO_Char(To_Date(A.Trade_Time,@#@#hh24miss@#@#),@#@#hh24:mi:ss@#@#) as TradeTime,B.BankName,C.AreaCode,C.AreaName,
                   (Case When CheckFlag=-1 Then @#@#/Images/state_rollback.gif@#@# When SuccFlag=-1 Then @#@#/Images/state_cancel.gif@#@# When StatusFlag=-1 Then @#@#/Images/state_unnormal.gif@#@# Else @#@#/Images/state_normal.gif@#@# End) as State,
                   A.CheckFlag,A.StatusFlag,A.SuccFlag
                 From Bank_Stream_Account A,BankInfoConfig B,AreaConfig C,SecAgtAccount D,FstAgtAccount E
                   Where A.Bank_ID=B.BankCode And Trim(A.Agent_Mobile)=D.AgtID And D.BossAgtID=E.AgtID And C.CityCode=E.CityCode @#;
 
  IF AreaCode!=@#0@# Then   --条件1： 选择所有地区的信息
    
     strSQL:=strSQL || @# And Trim(C.AreaCode)=@#@#@# || AreaCode || @#@#@# @#;
    
  End IF;  
    
  IF startTradeDate!=@# @# Then  --条件2： 选择所有地区的信息,选择所有交易开始日期的信息

     strSQL:=strSQL || @# And TO_Char(To_Date(A.Trade_Date,@#@#YYYYMMDD@#@#),@#@#YYYY-MM-DD@#@#)>=@#@#@# || startTradeDate || @#@#@# @#;       
          
  End If;
 
  IF endTradeDate!=@# @# Then  --条件2： 选择所有地区的信息,选择所有交易截止日期的信息

     strSQL:=strSQL || @# And TO_Char(To_Date(A.Trade_Date,@#@#YYYYMMDD@#@#),@#@#YYYY-MM-DD@#@#)<=@#@#@# || endTradeDate || @#@#@# @#;       
          
  End If;
 
  IF KeyWord!=@# @# Then  --条件3： 选择所有地区的信息,选择所有交易日期的信息,选择和查询关键字匹配的信息
    
     strSQL:=strSQL || @#  And (D.AgtID Like@#@#%@# || KeyWord || @#%@#@# Or TO_Char(A.BANK_GLIDE) Like @#@#%@# || KeyWord || @#%@#@# Or B.BankName Like @#@#%@# || KeyWord ||@#%@#@#) @#;
 
  End If; 
                
      
  Execute Immediate strSQL;    
    ---功能：把满足条件的二级代理商转帐信息导入数据库 End

 Open Re_CURSOR For @#Select * From Sms_pay.TEMPAgtBankFlow Order by AgtID@#;  --生成返回结果集的Curso
 
 ret:=0;
 return;

 Exception

  when others then
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
  ret:=999;  
  return;
  End GetAgtBankFlow;
 
 
 
 
  
 --功能描述：查询代理商钱包余额信息 writer: wang haibo 2004-08-24
 PROCEDURE GetAgtBalance(AreaCode in varchar2,KeyWord in varchar2,Re_CURSOR OUT T_CURSOR,ret out number)
   is
  pFSTAGTID VARCHAR2(13);
  pSECAgtID  VARCHAR2(13);
  pAgtBalance NUMBER(10,2);
  pAgtProfit  NUMBER(10,2);
  pAreaCode VARCHAR2(10);
  pAreaName VARCHAR2(30);
  pAgtCount NUMBER(10);
 
  IsExists number;
  subAgtNumber number;

 type c_GetBalance is ref cursor;

  FstCursor c_GetBalance;

  strSQL VARCHAR2(1024);
 

 Begin

  -- 打开游标，根据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 ;

 

原文转自：http://www.ltesting.net