pl/sql求查询语句

zhangfengyi 2012-04-25 02:42:50
以下存储过程返回查询结果集,统计结果. 现在的问题是:消费统计/调帐(借记)统计/调帐(贷记)统计/退货统计/充值统计,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;

...全文
79 1 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
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;
/

谢谢大家的支持,我会陆续上传相关电子书 由于体积较大,本书分两卷压缩,请都下载完再解压! Oracle 11g SQLPL SQL从入门到精通 pdf格式电子书 下载(一) http://download.csdn.net/source/3268267 Oracle 11g SQLPL SQL从入门到精通 pdf格式电子书 下载(二) http://download.csdn.net/source/3268312 内容简介   本书是专门为oracle应用开发人员提供的sqlpl/sql编程指南。通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法,而且可以掌握sql语句和pl/sql的各种基础知识和高级特征(记录类型、集合类型、对象类型、大对象类型)。   除了为读者提供编写sql语句和开发pl/sql块的方法外,本书还为应用开发人员提供了一些常用的pl/sql系统包。通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sqlpl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sqlpl/sql相关工具  第1章 在windows 平台上安装oracle database 11g  第2章 配置网络服务名  第3章 使用sql database  第4章 使用sql*plus 第二部分 sql  第5章 sqlpl/sql综述  第6章 简单查询  第7章 sql单行函数  第8章 操纵数据  第9章 复杂查询  第10章 管理常用对象 第三部分 pl/sql  第11章 pl/sql基础  第12章 访问oracle  第13章 编写控制结构  第14章 使用复合数据类型  第15章 使用游标  第16章 异常处理 . 第17章 本地动态sql  第18章 pl/sql过程  第19章 pl/sql函数  第20章 pl/sql包  第21章 触发器  第22章 使用对象类型 第四部分 pl/sql系统包  第23章 使用大对象  第24章 读写os文件  第25章 开发多媒体应用  第26章 开发web应用  第27章 dbms_sq动态sql  第28章 管理统计  第29章 使用数据库资源管理器  第30章 数据加密和解密  第31章 使用调度程序  第32章 使用flashback  第33章 使用重定义联机表  第34章 修正损坏块  第35章 使用日里民挖掘  第36章 使用管道  第37章 使用精细访问控制  第38章 使用精细审计  第39章 使用预警事件  第40章 转换rowid  第41章 其他常用包 习题答案

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧