IDC资讯中心
提示:在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 ;
