Oracle 查询存储过程做横向报表的方法
因为要牵扯到小计，所以需要计算两次。
想法：
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>
上一篇： c# 引用类型对象的深拷贝
下一篇： JavaScript实现简单轮播图效果