A package (stored procedure) that returns a recordset through a oracle8i stored procedure

Tip: In Oracle8i, if you need to return a result set through a stored procedure, you need to use a cursor!

CREATE OR REPLACE PACKAGE Body "Sms_pay". Smsmaint "
Is
--Functional Description: Inquiry Agent Bank transaction flow information Writer:wang Haibo 2004-08-24
PROCEDURE Getagtbankflow (AreaCode in Varchar2,keyword, varchar2,starttradedate in Varchar2,endtradedate, Re_cursor out T_cursor,ret out number)
Is
Isexists number;
strSQL VARCHAR2 (2048);
Begin
--Check whether the temporary table already exists, if it does not exist, create it, or insert data start
Select Count (*) into the isexists from All_tables Where table_name= ' Tempagtbankflow ';
IF isexists=0 Then
strsql:= ' CREATE GLOBAL temporary TABLE sms_pay. Tempagtbankflow
(Agtid VARCHAR2 (), bank_glide number (a), Tradetype VARCHAR2 (), Trade_money number (14,2),
Tradedate VARCHAR2 (a), Tradetime VARCHAR2 (8), Bankname VARCHAR2 (30),
AreaCode Varchar2, AreaName VARCHAR2 (), State VARCHAR2 (1), Checkflag
Statusflag number (1), Succflag number (1)) on commit preserve rows ';
--Change the creation option of the temporary table from on commit Delete to on commit preserve rows;
Otherwise, the Ora-08103:object no longer is returned when the call occurs exists
Execute Immediate strSQL;
Else
Execute immediate ' Truncate table Sms_pay. Tempagtbankflow ';
End IF;

--Check whether the temporary table already exists, if it does not exist, create it, or insert the data end

---function: The first level agent to meet the requirements of the transfer information into the database Start

/*strsql:= ' Insert into Tempagtbankflow
Select A.agent_mobile,a.bank_glide,
Decode (A.optcode, ' 100 ', ' ' Payment transaction ', ' ' 101 ', ' ' pay-forward ', ' ' 900 ', ' ' The ' account Transaction ', ' ' type unknown ') 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 ') 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-condition 1: Select all areas of information
Strsql:=strsql | | ' and Trim (c.areacode) = ' ' | | AreaCode | | ''' ';
End IF;
IF tradedate!= ' Then-Condition 2: Select all information about the area and select all transaction dates
Strsql:=strsql | | ' and To_char (To_date (a.trade_date, ' YYYYMMDD '), ' yyyy-mm-dd ') = ' ' | | Tradedate | | ''' ';
End If;
IF keyword!= ' Then-Condition 3: Select all the information in the region, select all transaction date information, select and query keyword matching information
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;
---function: The first level agent to meet the requirements of the transfer information into the database end
*/