--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;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货