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