• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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");
                MapString, Object> paraMap = new HashMapString, 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>

    您可能感兴趣的文章:
    • oracle求和代码示例
    • oracle横向纵向求和代码实例
    上一篇:Oracle创建自增字段--ORACLE SEQUENCE的简单使用介绍
    下一篇:ORACLE SQL-UPDATE、DELETE、INSERT优化和使用技巧分享
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Oracle 查询存储过程做横向报表的方法 Oracle,查询,存储,过程,做,