17,377
社区成员
发帖
与我相关
我的任务
分享pl/sql求查询语句
以下存储过程返回查询结果集,统计结果. 现在的问题是:消费统计/调帐(借记)统计/调帐(贷记)统计/退货统计/充值统计,5项结果不正确,要求从"结果集"中进行统计,现在不是,我该如果做
--OK以此为准2012-4-17
create or replace package pak_trans as
type trans_cursor is ref cursor;
end pak_trans;
/
CREATE OR REPLACE PROCEDURE "CARD"."TRANS"
(
mcht in varchar, --商户号
sett in varchar, --清算号
startcardno in varchar, --起始卡号
endcardno in varchar, --结束卡号
tx_code in varchar, --交易代码
banlace in varchar, --交易金额
startdate in varchar, --开始日期
enddate in varchar, --结束日期
traceno in varchar, --流水号
batchno in varchar, --批次号
termid in varchar, --终端号
document in varchar, --证件类型
cardhold in varchar, --证件号码
lxname in varchar, --联系人
telphone in varchar, --电话
gkdate in varchar, --购卡时间
pagesize in number, --页尺寸
currpage in number, --当前页
recordcount out number, --记录数
sell out number, --消费笔数
rating_sum out number, --消费金额
rating out number, --充值笔数
init_sum out number, --充值金额
initsell out number, --退货笔数
RSA_sum out number, --退货金额
init out number, --调帐(借记)笔数
TSO_sum out number, --调帐(借记)金额
makeall out number, --调帐(贷记)笔数
TSI_sum out number, --调帐(贷记)金额
poundage out number, --卡激活总量
hstint out number, --卡冻结总量
intbal out number, --卡解冻总量
p_cursor out pak_trans.trans_cursor --返回结果集
)
as
v_startRowNum NUMBER; --分页起始记录编号
v_endRowNum NUMBER; --分页结束记录编号
strsql varchar2(4000); --查询语句
strsql1 varchar2(4000); --查询语句1
str varchar2(4000);
sqlwhere varchar2(4000); --where条件1
sqlwhere1 varchar2(4000); --where条件2
sqlwhere2 varchar2(4000); --where条件3
sqlwhere3 varchar2(4000); --where条件4
sqlwhere4 varchar2(4000); --where条件5
sqlwhere5 varchar2(4000); --where条件6
sqlwhere6 varchar2(100); --where条件7
perstr varchar2(4000); --充值SQL
rej_code varchar2(10); --卡状态变更
txcode varchar(5); --卡状态变更状态码
standby2 varchar2(40); --卡段
tmp_min varchar2(7); --卡段最小值
tmp_max varchar2(7); --卡段最大值
begin
--查询条件
sqlwhere:=' where merch_id='
||mcht
||' and sett_postdate>='''
||startdate
||''' and sett_postdate<='''
||enddate
||''' and exists(select * from merchant where merch_id='
||mcht
--||' and exists(select * from grppou where substr(MERGRP,1,8)='
--||substr(merchant.grp_name,1,8)
--||' and grp_leader='
--||mcht
||')';
if length(startcardno)>0 and length(endcardno)>0 then
sqlwhere:=sqlwhere
||' and cardno>='''
||startcardno
||''' and cardno<='''
||endcardno
||'''';
--||' or (substr(addidata1,1,18)<='
--||startcardno
--||' and substr(addidata1,20,18)>='
--||startcardno
--||' and substr(addidata1,1,18)<='
--||endcardno
--||' and substr(addidata1,20,18)>='
--||endcardno
--||')';
end if;
if to_number(banlace)!=0 then
sqlwhere:=sqlwhere
||' and req_txnamt='
||to_number(banlace)*0.01;
end if;
if length(traceno)>0 then
sqlwhere:=sqlwhere
||' and trace_no='
||traceno;
end if;
if length(batchno)>0 then
sqlwhere:=sqlwhere
||' and batchid='
||batchno;
end if;
if length(termid)>0 then
sqlwhere:=sqlwhere
||' and term_id='
||termid;
end if;
if length(cardhold)>0 then
sqlwhere:=sqlwhere
||' and addidata1 like '''
||'%'
||cardhold
||'%''';
end if;
if length(lxname)>0 then
sqlwhere:=sqlwhere
||' and addidata1 like '''
||'%'
||lxname
||'%''';
end if;
if length(telphone)>0 then
sqlwhere:=sqlwhere
||' and addidata1 like '''
||'%'
||telphone
||'%''';
end if;
if length(gkdate)>0 then
sqlwhere:=sqlwhere
||' and addidata1 like '''
||'%'
||gkdate
||'%''';
end if;
--where 条件2
sqlwhere1:=' and (compl_flag<>04 and compl_flag<>02 or compl_flag is null) and rsp_code=00 and tx_code=''PER''';
sqlwhere2:=' and (compl_flag<>04 and compl_flag<>02 or compl_flag is null) and rsp_code=00 and tx_code=''TSO''';
sqlwhere3:=' and (compl_flag<>04 and compl_flag<>02 or compl_flag is null) and rsp_code=00 and tx_code=''TSI''';
sqlwhere4:=' and (compl_flag<>04 and compl_flag<>02 or compl_flag is null) and rsp_code=00 and (tx_code=''RSA'' or tx_code=''REF'')';
sqlwhere5:=' and (compl_flag<>04 and compl_flag<>02 or compl_flag is null) and rsp_code=00 and (tx_code=''HNT'' or tx_code=''PNT'')';
--sqlwhere6:=' and (tx_code=''CST'' or tx_code=''HSN'' or tx_code=''PSN'')';
if length(tx_code)>0 then
sqlwhere6:=' and tx_code='''
||tx_code
||'''';
end if;
...全文
请发表友善的回复…
发表回复
zhangfengyi 2012-04-25
- 打赏
- 举报
--if tx_code='PER' then --消费
-- sqlwhere1:=sqlwhere1||' and tx_code=PER';
--end if;
--if tx_code='TSO' then --调账(借记)
-- sqlwhere1:=sqlwhere1||' and tx_code=TSO';
--end if;
--if tx_code='TSI' then --调账(贷记)
-- sqlwhere1:=sqlwhere1||' and tx_code=TSI';
--end if;
--统计记录总数
strsql:='select count(*) from (select * from partranslog'
||sqlwhere
||sqlwhere6
||' union select * from translog'
||sqlwhere
||' union select * from webtranslog'
||sqlwhere
||sqlwhere6
||')';
execute immediate strsql into recordcount;
--确定起始页与结束页
v_startRowNum:=(currpage-1)*pagesize+1;
if currpage*pagesize>recordcount then
v_endRowNum:=recordcount;
else
v_endRowNum:=currpage*pagesize;
end if;
--消费统计
strsql:='select count(*),nvl(sum(nvl(tranamount,0)),0) from (select * from partranslog'
||sqlwhere
||sqlwhere1
||' union select * from translog'
||sqlwhere
||sqlwhere1
||' union select * from webtranslog'
||sqlwhere
||sqlwhere1
||')';
execute immediate strsql into sell,rating_sum;
--调帐(借记)统计
strsql:='select count(*),nvl(sum(nvl(tranamount,0)),0) from (select * from partranslog'
||sqlwhere
||sqlwhere2
||' union select * from translog'
||sqlwhere
||sqlwhere2
||' union select * from webtranslog'
||sqlwhere
||sqlwhere2
||')';
execute immediate strsql into init,TSO_sum;
--update proce set name=TSO_sum;
--调帐(贷记)统计
strsql:='select count(*),nvl(sum(nvl(tranamount,0)),0) from (select * from partranslog'
||sqlwhere
||sqlwhere3
||' union select * from translog'
||sqlwhere
||sqlwhere3
||' union select * from webtranslog'
||sqlwhere
||sqlwhere3
||')';
execute immediate strsql into makeall,TSI_sum;
--退货统计
strsql:='select count(*),nvl(sum(nvl(tranamount,0)),0) from (select * from partranslog'
||sqlwhere
||sqlwhere4
||' union select * from translog'
||sqlwhere
||sqlwhere4
||' union select * from webtranslog'
||sqlwhere
||sqlwhere4
||')';
execute immediate strsql into initsell,RSA_sum;
--充值统计
strsql:='select count(*),nvl(sum(nvl(tranamount,0)),0) from (select * from partranslog'
||sqlwhere
||sqlwhere5
||' union select * from translog'
||sqlwhere
||sqlwhere5
||' union select * from webtranslog'
||sqlwhere
||sqlwhere5
||')';
execute immediate strsql into rating,init_sum;
poundage:=0;
hstint:=0;
intbal:=0;
--卡状态变更统计
strsql:='select tx_code,rej_code,addidata1 from (select * from partranslog'||sqlwhere||' and (tx_code=''CST'' or tx_code=''HSN'') union select * from translog'||sqlwhere||' and (tx_code=''CST'' or tx_code=''HSN'' or tx_code=''PSN'') union select * from webtranslog'||sqlwhere||' and (tx_code=''CST'' or tx_code=''HSN''))';
OPEN p_cursor FOR strsql;
LOOP
EXIT WHEN p_cursor%NOTFOUND;
FETCH p_cursor INTO txcode,rej_code,standby2;
if txcode='CST' or txcode='HSN' or txcode='PSN' then
if rej_code='1' then
if length(standby2)>36 then
tmp_min:=to_number(substr(standby2,11,7));
tmp_max:=to_number(substr(standby2,30,7));
poundage:=poundage+(tmp_max-tmp_min+1);
else
poundage:=poundage+1;
end if;
end if;
if rej_code='3' then
if length(standby2)>36 then
tmp_min:=to_number(substr(standby2,11,7));
tmp_max:=to_number(substr(standby2,30,7));
hstint:=hstint+(tmp_max-tmp_min+1);
else
hstint:=hstint+1;
end if;
end if;
if rej_code='4' then
if length(standby2)>36 then
tmp_min:=to_number(substr(standby2,11,7));
tmp_max:=to_number(substr(standby2,30,7));
intbal:=intbal+(tmp_max-tmp_min+1);
else
intbal:=intbal+1;
end if;
end if;
end if;
END LOOP;
--update proce set name=strsql;
CLOSE P_CURSOR;
--查询结果
strsql:='select * from (select a.tran_cn,t1.cardno,t1.trandate,t1.sett_postdate,t1.merch_id,t1.req_txnamt,t1.trace_no,t1.sys_no,t1.compl_flag,t1.rsp_code,t1.input_mode,t1.term_id,t1.integral,t1.valid_date,t1.mer_orderno,t1.addidata1,q.cnname,rownum rn from isoconvert a, qudaopar q, (select * from partranslog'
||sqlwhere
||sqlwhere6
||' union select * from translog'
||sqlwhere
||sqlwhere6
||' union select * from webtranslog'
||sqlwhere
||sqlwhere6
||') t1 where a.tran_name=t1.tx_code and to_number(substr(t1.TRAN_SLA_KEY,17,5))=q.instcode) where rn<='
||v_endRowNum
||' and rn>='
||v_startRowNum;
OPEN p_cursor FOR strsql;
--update proce set name=strsql;
end trans;
/
