Tip: In Oracle8i, if you need to return the result set by the stored procedure! Create or Replace package body "sms_pay". "SMSMAINT" IS - Function Description: Query agent Bank Trading Information 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
- Check whether the temporary table already exists, if does not exist, create, or insert data 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';
- Change the creation option of the temporary table by on commit delete rows; - otherwise an ORA-08103: Object No longeediate IMMEDIATETSQL; Else Execute Immediate 'Truncate Table SMS_PAY. Tempagtbankflow '; end if; - Check if the temporary table already exists, if there is no existence, create, otherwise insert data end - function: import the level agent transfer information for the condition to import the database start / * strsql: =' INSERT INTO TEMPAGTBANKFLOW SELECT A.AGENT_MOBILE, A.BANK_GLIDE, DECODE (A.OPTCode, '' '' ',' 'payment transaction', '' 101 ',' 'payment rushing "', '' 900 ',' '' '' '' '' '' '' '' '' '' '' '' '' '' As tradedate, to_char (to_date (a.trade_time, '' hh24miss '),' 'HH24: MI: SS') AS Tradetime, B. Bankname, C.areAracode, 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, BankInfoc B, AreaConfig C, FSTAGTACCOUNT D Where A.BANK_ID = B.Bankcode and Trim (a.Agent_Mobile) = D.AGTID and c.citycode = d.citycode '; if aacode! =' 0 'THEN - Condition 1: Select Information in all regions strsql: = strsQL || 'and trim (c.areAracode) =' '|| AreaCode ||' ''; end if; if tradedate! = 'THEN - Condition 2: Select all regions Information, select all trading dates
Strsql: = strsql || 'and to_char (to_date (a.trade_date,' 'YYYYMMDD'), '' YYYY-MM-DD '') = '' || tradedate || '' '; endiff; If Keyword! = '' THEN - Condition 3: Select information in all regions, select all trading dates, select and query keyword matching information strsql: = strsql || 'and (d.agtid like' '%' || Keyword || '%' or to_char (a.bank_glide) Like ''% '|| Keyword ||'% '' ory '|| kyword ||'% '') ' Endness; execute immediate strsql; --- Features: Import the level of the first-level agent transfer information to the condition to import the database END * / --- function: Import the secondary agent transfer information that meet the conditions into the database Start strsQL: = ' INSERT INTO TEMPAGTBANKFLOW SELECT A.AGENT_MOBILE, A.BANK_GLIDE, DECODE (A.OPTCode, '' '' ',' 'payment transaction', '' 101 ',' 'payment rushing "', '' 900 ',' '' '' '' '' '' '' '' '' '' '' '' '' '' As tradedate, to_char (to_date (a.trade_time, '' hh24miss '),' 'HH24: MI: SS') AS Tradetime, B. Bankname, C.areAracode, C.areaname, (Case When Checkflag = -1 THEN '' / images / state_rollback .gif '' /iMages/state_cancel.gif '' When Statusflag = -1 TEN '' /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 aacode! =' 0 'THEN - Condition 1: Select information in all regions Strsql: = strsql ||' and Trim (C.areAcode) = '' '|| Area; EndiffE; if StartTradedate! =' 'THEN - Condition 2: Select information in all regions, select all the transaction starts Date STRSQL: = strsql ||' and TO_CHAR (TO_DATE (A.TRADE_DATE, '' YYYYMMDD '),' 'YYYY-MM-DD' ')> =' '|| StartTradedate ||' '' '; end if; if endtradedate! =' 'THEN - Conditions 2: Select information from all regions to select all transaction deadlines
strsql: = strsql || 'and to_char (to_date (a.trade_date,' 'YYYYMMDD'), '' YYYY-MM-DD '') <= '' || endtradedate || '' '; endiff ; If Keyword! = '' THEN - Condition 3: Select all the information, select all the information of all trading dates, 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: Import the secondary agent transfer information of the condition to import the database
Open re_cursor for 'select * from sms_pay.tempagtbankflow order by agtid'; - Generates the CURSO RET: = 0 of the return result set; Return;
EXCEPTION
when others then DBMS_OUTPUT.PUT_LINE (SQLERRM); ret: = 999; return; End GetAgtBankFlow; - Description: Query agents wallet balance information 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
- Open the cursor, get Level 1 agent information according to the SQL statement, then obtain the level 2 agent information according to the level 1 agent's information
If aacode! = '0' and keyword = '' Then strsql: = 'select a.agtid,' '', a.agtcredit, a.agtprofit, b.areche, b.areaname, 0 from sms_pay.fstagtaccount A, SMS_PAY.Areaconfig B Where a.isactive = 1 and a.citycode = B.CITYCODE AND B.Aracode = '' '|| AreaCode ||' ''; Elsif (Trim (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.areconfig b Where a.isactive = 1 and a.citycode = b.citycode and b.areAcode = '' || area || '' '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.arecode, b.areaname, 0 from sms_pay.fstagtaccount a, sms_pay.areaconfig b where a.isactive = 1 and a.citycode = b.citycode'; Else STRS QL: = 'SELECT A.agtid,' '', a.agtcredit, a.agtprofit, b.arecode, 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 ||' '' '|| Keyword ||'% '') '; end if; Open Fstcursor For strsql;
- Check if the temporary table already exists. If there is no existence, create, otherwise insert data start select count (*) 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';
- Change the creation of the temporary table is changed from on commit delete rows to on Commit Preserve Rows; Else Execute IMMEDIATE 'TRUNCATE TABLE SMS_PAY.TEMPAGTBALANCE'; END IF; - Check if the temporary table already exists, if not, create, otherwise insert data End loop
Fetch Fstcursor Into PfstagtiD, PsecagtiD, Pagtbalance, Pampprofit, Pareacode, Pareaname, PAGTCOUNT; EXIT WHEN FSTCURSOR% NOTFOUND
--1: Write Level 1 Agent Query Information 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: Write Level 2 agent query information strsql: =' INSERT INTO TEMPAGTBALANCE SELECT NULL, AGTID, '' '| | PfstagtiD ||' ', Agtcredit, AgtProfit,' '| | Pareacode || '', '' || Pareaname || '' ', 0 from secagtaccount where trim (bossagtid) =' '|| pfstagtid ||' ''; Execute Immediate strsql; --3: Updating the number of lower-level agents at Level 1 Agent STRSQL: = 'SELECT Count (*) from TempagtBalance Where Sectid 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;
