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