Oracle 查询存储过程做横向报表的方法
发布时间:2024-01-16 00:59:55
因为要牵扯到小计,所以需要计算两次。
想法:
1、把查询到的结果,插入到临时表,
2、把统计结果插入到临时表。
3、查询临时表记录放置到游标中。
4、删除临时表记录。
包的定义声明:
CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IS
type cursor_type is ref cursor;
/************************************************************************************/
/* 功能说明:查询某种公告报表 */
/* 参数说明: */
/* i_id_capital_dynamic_manage IN VARCHAR2 某种公告ID */
/* o_cursor OUT cursor_type 返回游标 */
/* */
/* 创建日期 姓名 */
/* 2013-03-08 路人甲 */
/************************************************************************************/
PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manage IN VARCHAR2,
o_cursor OUT cursor_type);
END CHEN_TEST_PACKGE;
包的实现:
CREATE OR REPLACE PACKAGE BODY CHEN_TEST_PACKGE IS
/************************************************************************************/
/* 功能说明:查询某种公告报表 */
/* 参数说明: */
/* i_id_capital_dynamic_manage IN VARCHAR2 某种公告ID */
/* o_cursor OUT bulletin_report_type 返回游标 */
/* */
/* 创建日期 姓名 */
/* 2013-03-08 路人甲 */
/************************************************************************************/
PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manage IN VARCHAR2,
o_cursor OUT bulletin_report_type)
AS
set_id_bulletin_report_temp VARCHAR2(50); -- 定义临时变量
BEGIN
begin
--给临时变量赋值
--select to_char(sysdate,'yyyymmddhh24missSSS') into set_id_bulletin_report_temp from dual;
select i_id_capital_dynamic_manage into set_id_bulletin_report_temp from dual;
--获取数据插入临时表
insert into scms_bulletin_report_temp
(
id_bulletin_report_temp,
biz_Name ,
t01 ,
t07 ,
t14 ,
t21 ,
t1M ,
t2M ,
t3M ,
t4M ,
t5M ,
t6M ,
t1Y ,
t2Y ,
tCount ,
sort_no
)
select c.*,
rownum as sort_no
from(
select
set_id_bulletin_report_temp as id_bulletin_report_temp,
scms_common_packge.get_biz_name(b.biz_id) as biz_Name,
max(case when b.term_type='T01' then b.c else 0 end) as T01,
max(case when b.term_type='T07' then b.c else 0 end) as T07,
max(case when b.term_type='T14' then b.c else 0 end) as T14,
max(case when b.term_type='T21' then b.c else 0 end) as T21,
max(case when b.term_type='T1M' then b.c else 0 end) as T1M,
max(case when b.term_type='T2M' then b.c else 0 end) as T2M,
max(case when b.term_type='T3M' then b.c else 0 end) as T3M,
max(case when b.term_type='T4M' then b.c else 0 end) as T4M,
max(case when b.term_type='T5M' then b.c else 0 end) as T5M,
max(case when b.term_type='T6M' then b.c else 0 end) as T6M,
max(case when b.term_type='T1Y' then b.c else 0 end) as T1Y,
max(case when b.term_type='T2Y' then b.c else 0 end) as T2Y,
sum(b.c) as BIZ_ID_COUNT
from
(
select a.term_type,a.biz_id, sum(a.capital_claim) c
from (select report.capital_claim,
report.biz_id,
detail.term_type
from scms_capital_claim_report report,
scms_capital_assign_detail detail,
scms_capital_dynamic_manage manager
where manager.id_capital_dynamic_manage = detail.id_capital_dynamic_manage
and report.id_capital_assign_detail = detail.id_capital_assign_detail
and detail.id_capital_dynamic_manage = i_id_capital_dynamic_manage
and manager.IS_SETTLEMENT = '1'
and manager.IS_CONFIRM = '1'
) a
group by a.term_type, a.biz_id
) b group by b.biz_id
) c;
-- 插入总记录数
insert into scms_bulletin_report_temp
(
id_bulletin_report_temp,
biz_Name ,
t01 ,
t07 ,
t14 ,
t21 ,
t1M ,
t2M ,
t3M ,
t4M ,
t5M ,
t6M ,
t1Y ,
t2Y ,
tCount ,
sort_no
)
select c.*,
(select max(sort_no)+1 from scms_bulletin_report_temp te where te.id_bulletin_report_temp = set_id_bulletin_report_temp ) as sort_no
from(
select
set_id_bulletin_report_temp as id_bulletin_report_temp,
'总计(天数)' as biz_Name,
max(case when b.term_type='T01' then b.c else 0 end) as T01,
max(case when b.term_type='T07' then b.c else 0 end) as T07,
max(case when b.term_type='T14' then b.c else 0 end) as T14,
max(case when b.term_type='T21' then b.c else 0 end) as T21,
max(case when b.term_type='T1M' then b.c else 0 end) as T1M,
max(case when b.term_type='T2M' then b.c else 0 end) as T2M,
max(case when b.term_type='T3M' then b.c else 0 end) as T3M,
max(case when b.term_type='T4M' then b.c else 0 end) as T4M,
max(case when b.term_type='T5M' then b.c else 0 end) as T5M,
max(case when b.term_type='T6M' then b.c else 0 end) as T6M,
max(case when b.term_type='T1Y' then b.c else 0 end) as T1Y,
max(case when b.term_type='T2Y' then b.c else 0 end) as T2Y,
sum(b.c) as BIZ_ID_COUNT
from
(
select a.term_type,'biz_id_count' as biz_id, sum(a.capital_claim) c
from (select report.capital_claim,
report.biz_id,
detail.term_type
from scms_capital_claim_report report,
scms_capital_assign_detail detail,
scms_capital_dynamic_manage manager
where manager.id_capital_dynamic_manage = detail.id_capital_dynamic_manage
and report.id_capital_assign_detail = detail.id_capital_assign_detail
and detail.id_capital_dynamic_manage = i_id_capital_dynamic_manage
and manager.IS_SETTLEMENT = '1'
and manager.IS_CONFIRM = '1'
) a
group by a.term_type
) b group by b.biz_id
) c;
-- 查询刚刚插入的表记录
open o_cursor for
select
id_bulletin_report_temp as idBulletinReportTemp,
biz_Name as bizName ,
t01 as t01 ,
t07 as t07 ,
t14 as t14 ,
t21 as t21 ,
t1M as t1M ,
t2M as t2M ,
t3M as t3M ,
t4M as t4M ,
t5M as t5M ,
t6M as t6M ,
t1Y as t1Y ,
t2Y as t2Y ,
tCount as tCount,
sort_no as sortNo
from scms_bulletin_report_temp temp
where temp.id_bulletin_report_temp = set_id_bulletin_report_temp
order by sortNo asc;
-- 删除:根据ID删除刚刚插入的记录
delete from scms_bulletin_report_temp temp where temp.id_bulletin_report_temp = set_id_bulletin_report_temp;
commit;
end;
END p_list_bulletin_report;
END CHEN_TEST_PACKGE;
/
页面调用ibatis的xml配置查询结果:
<!-- 某种公告报表查询 结果集 class="java.util.HashMap"-->
<resultMap id="bulletinReportResultMap" class="java.util.TreeMap">
<result property="bizId" column="bizId"/>
<result property="bizName" column="bizName"/>
<result property="t01" column="t01"/>
<result property="t07" column="t07"/>
<result property="t14" column="t14"/>
<result property="t21" column="t21"/>
<result property="t1M" column="t1M"/>
<result property="t2M" column="t2M"/>
<result property="t3M" column="t3M"/>
<result property="t4M" column="t4M"/>
<result property="t5M" column="t5M"/>
<result property="t6M" column="t6M"/>
<result property="t1Y" column="t1Y"/>
<result property="t2Y" column="t2Y"/>
<result property="tCount" column="tCount"/>
<result property="sortNo" column="sortNo"/>
</resultMap>
<!-- 某种公告报表查询 参数 -->
<parameterMap id="bulletinReportParamMap" class="java.util.Map">
<parameter property="i_id_capital_dynamic_manage" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
<parameter property="o_cursor" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT" />
</parameterMap>
<!-- 某种公告报表查询 调用存储过程 -->
<procedure id="queryBulletinReportList" resultMap="bulletinReportResultMap" parameterMap="bulletinReportParamMap">
{call CHEN_TEST_PACKGE.p_list_bulletin_report(?,?)}
</procedure>
java调用:
String id_capital_dynamic_manage = request.getParameter("id_capital_dynamic_manage");
Map<String, Object> paraMap = new HashMap<String, Object>();
paraMap.put("i_id_capital_dynamic_manage", id_capital_dynamic_manage);
// 调用存储过程,查询
List resultList = (List<?>) CURDUtil.queryList("queryBulletinReportList", paraMap);
页面显示jsp:
<c:if test="${not empty msgList}">
<c:forEach items="${msgList}" var="item">
<tr align="center" >
<td nowrap class="td_3" ><c:out value="${item.bizName}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t01}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t07}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t14}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t21}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t1M}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t2M}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.t3M}"/></td>
<td nowrap class="td_3" ><fmt:formatNumber pattern="#,##0.00" value="${item.tCount}"/></td>
</tr>
</c:forEach>
</c:if>
猜你喜欢
- 如下所示:# 返回一个列表中第二大的数def second(ln):max = 0s = {}for i in range(len(ln))
- 一、匹配版本基于Camunda 7.16.0 + Springboot 2.5.8首先我们去官网找到camunda7.16对应的spring
- 一、AIML是什么AIML全名为Artificial Intelligence Markup Language(人工智能标记语言),是一种创
- 本文介绍基于Python语言,针对一个文件夹下大量的Excel表格文件,基于其中每一个文件,随机从其中选取一部分数据,并将全部文件中随机获取
- 这些小东西是我在网上看到的就把它记下来了,可能以后会有用的: &nbs
- 区别与联系: 1、get是从服务器上获取数据,post则是向服务器传送数据; 2、get将表单中数据的按照variable=value的 形
- TEMPLATESDjango 1.8的新特性一个列表,包含所有在Django中使用的模板引擎的设置。列表中的每一项都是一个字典,包含某个引
- 大家都知道一些论坛的标题有高亮显示功能,在这里我不讨论也不研究论坛普遍的实现方法,下面是我的实现方法:实现思路:把要高亮显示的标题加上特定标
- 装饰器一、介绍器:代表函数的意思。装饰器本质就是是函数功能:装饰其他函数,就是为其他函数添加附加功能 被装饰函数感受不到装饰器的存
- 但凡有些事情重复时,我就在想怎么可以用程序来自动化。这里想分享如何每天给女友定时微信发送”晚安“,如果只是晚安,就略显单调,于是爬取金山词霸
- 我就废话不多说了,大家还是直接看代码吧~// 窗体透明,控件不透明self.setWindowFlags(Qt.FramelessWindo
- 如: 0.625 取 1 2.1 取3 3.6 取4 <% if fix(a)>a then b=fix(a) else b=f
- 该章节将学习关于文件查找的操作,大家都知道,无论是 Linux 系统还是 Windows 系统都有基于文件名实现过滤、查找的功能。但是如果想
- 爬蟲四步原理:1.发送请求:requests2.获取相应数据:对方及其直接返回3.解析并提取想要的数据:re4.保存提取后的数据:with
- <html><head><meta http-equiv="Content-T
- 本文实例讲述了PHP字典树(Trie树)定义与实现方法。分享给大家供大家参考,具体如下:Trie树的概念(百度的解释):字典树又称单词查找树
- 1.列表:list# 1.list:Python内置的一种数据类型,列表;# 2.list是一种有序的集合,可以随时添加和删除其中的元素;#
- 文章摘要:其实这里casperjs与python没有直接关系,主要依赖casperjs调用phantomjs webkit获取html文件内
- 本文实例为大家分享了python实现学生成绩管理系统的具体代码,供大家参考,具体内容如下需求:代码:import osfilename =
- 1 StreamingHttpResponse下载StreamingHttpResponse(streaming_content):流式相应