分享
 
 
 

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

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有