Oracle 查詢存儲(chǔ)過(guò)程做橫向報(bào)表的方法
因?yàn)橐獱砍兜叫∮?jì)，所以需要計(jì)算兩次。
想法：
1、把查詢到的結(jié)果，插入到臨時(shí)表，
2、把統(tǒng)計(jì)結(jié)果插入到臨時(shí)表。
3、查詢臨時(shí)表記錄放置到游標(biāo)中。
4、刪除臨時(shí)表記錄。
包的定義聲明：
CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IS
type cursor_type is ref cursor;
/************************************************************************************/
/* 功能說(shuō)明：查詢某種公告報(bào)表 */
/* 參數(shù)說(shuō)明： */
/* i_id_capital_dynamic_manage IN VARCHAR2 某種公告ID */
/* o_cursor OUT cursor_type 返回游標(biāo) */
/* */
/* 創(chuàng)建日期 姓名 */
/* 2013-03-08 路人甲 */
/************************************************************************************/
PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manage IN VARCHAR2,
o_cursor OUT cursor_type);
END CHEN_TEST_PACKGE;
包的實(shí)現(xiàn)：
CREATE OR REPLACE PACKAGE BODY CHEN_TEST_PACKGE IS
/************************************************************************************/
/* 功能說(shuō)明：查詢某種公告報(bào)表 */
/* 參數(shù)說(shuō)明： */
/* i_id_capital_dynamic_manage IN VARCHAR2 某種公告ID */
/* o_cursor OUT bulletin_report_type 返回游標(biāo) */
/* */
/* 創(chuàng)建日期 姓名 */
/* 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); -- 定義臨時(shí)變量
BEGIN
begin
--給臨時(shí)變量賦值
--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;
--獲取數(shù)據(jù)插入臨時(shí)表
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;
-- 插入總記錄數(shù)
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,
'總計(jì)（天數(shù)）' 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;
-- 刪除：根據(jù)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;
/
頁(yè)面調(diào)用ibatis的xml配置查詢結(jié)果：
<!-- 某種公告報(bào)表查詢 結(jié)果集 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>
<!-- 某種公告報(bào)表查詢 參數(shù) -->
<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>
<!-- 某種公告報(bào)表查詢 調(diào)用存儲(chǔ)過(guò)程 -->
<procedure id="queryBulletinReportList" resultMap="bulletinReportResultMap" parameterMap="bulletinReportParamMap">
{call CHEN_TEST_PACKGE.p_list_bulletin_report(?,?)}
</procedure>
java調(diào)用：
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);
// 調(diào)用存儲(chǔ)過(guò)程，查詢
List resultList = (List<?>) CURDUtil.queryList("queryBulletinReportList", paraMap);
頁(yè)面顯示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>
相關(guān)文章
如何實(shí)現(xiàn)只授予用戶查看存儲(chǔ)過(guò)程定義的權(quán)限
本文對(duì)如何授予某個(gè)用戶只能查看某些存儲(chǔ)過(guò)程的定義權(quán)限，而不能讓用戶去修改、執(zhí)行存儲(chǔ)過(guò)程，進(jìn)行了梳理和總結(jié)，供大家參考。2015-09-09
ORACLE數(shù)據(jù)庫(kù)日常維護(hù)知識(shí)點(diǎn)總結(jié)
這篇文章主要介紹了ORACLE數(shù)據(jù)庫(kù)日常維護(hù)知識(shí)點(diǎn)總結(jié)，對(duì)于維護(hù)oracle數(shù)據(jù)庫(kù)的朋友可以參考下2016-06-06
這篇文章介紹了Oracle去除重復(fù)數(shù)據(jù)的方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
Oracle數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出超詳細(xì)教程
最近做項(xiàng)目的時(shí)候遇到過(guò)oracle數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出,在這里我做下記錄,防止自己忘記了,下面這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出的相關(guān)資料,需要的朋友可以參考下2023-12-12
Oracle表空間數(shù)據(jù)庫(kù)文件收縮案例解析
這篇文章主要介紹了Oracle表空間數(shù)據(jù)庫(kù)文件收縮案例解析,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-07-07
Oracle通過(guò)時(shí)間(分鐘)計(jì)算有幾天幾小時(shí)幾分鐘的方法
這篇文章主要介紹了Oracle通過(guò)時(shí)間(分鐘)計(jì)算有幾天幾小時(shí)幾分鐘的方法,涉及Oracle時(shí)間操作的技巧,需要的朋友可以參考下2015-03-03
Oracle 細(xì)粒度審計(jì)(FGA)初步認(rèn)識(shí)
細(xì)粒度審計(jì)(FGA)，是在Oracle 9i中引入的,能夠記錄SCN號(hào)和行級(jí)的更改以重建舊的數(shù)據(jù),本文將詳細(xì)介紹，需要的朋友可以參考下2012-12-12![[Oracle] 淺析令人抓狂的ORA-01555問(wèn)題](http://img.jbzj.com/images/xgimg/bcimg7.png)
[Oracle] 淺析令人抓狂的ORA-01555問(wèn)題
首先要說(shuō)明的一點(diǎn)是：ORA-01555錯(cuò)誤是一個(gè)“安全”的錯(cuò)誤，它不會(huì)造成數(shù)據(jù)丟失或損壞，只是會(huì)讓接收到該錯(cuò)誤的查詢無(wú)法繼續(xù)。以下就是對(duì)發(fā)生ORA-01555錯(cuò)誤的原因進(jìn)行了介紹，需要的朋友可以參考下2013-07-07
oracle執(zhí)行cmd的實(shí)現(xiàn)方法
裝了一個(gè)oracle db11g，于是想試一下網(wǎng)上流傳的在sqlplus中執(zhí)行cmd的一些命令，也不知怎么的，沒一個(gè)好用的，可能是網(wǎng)上轉(zhuǎn)來(lái)轉(zhuǎn)去的轉(zhuǎn)錯(cuò)了．2009-04-04
PL/SQL 類型格式轉(zhuǎn)換...2007-03-03