。

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; / 。

  。

<!-- 某种公告报表查询 结果集 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> 。

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

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