程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 一個通過Oracle8i存儲過程返回記錄集的程序包(存儲過程)

一個通過Oracle8i存儲過程返回記錄集的程序包(存儲過程)

編輯:Oracle數據庫基礎

提示:在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),SuccFlag 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 ;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved