一个通过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),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;