Oracle 查詢存儲過程做橫向報表的方法

2020-07-26 14:27:17

字體：大中小

來源：轉載

供稿：網友

因為要牽扯到小計，所以需要計算兩次。

想法：

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配置查詢結果：

復制代碼代碼如下:

<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>