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>

(0)

相关推荐

  • oracle横向纵向求和代码实例

    有一张工资表SALARY如下, (NO 员工编号 ,MONEY 工资) NO    NAME     ITEM       MONEY 001    张三        工资        80 001    张三        补贴        86 001    张三        奖金        75 002    李四        工资        78 002    李四        补贴        85 002    李四        奖金        78 求每

  • oracle求和代码示例

    有一张工资表SALARY如下, (NO 员工编号 ,MONEY 工资) NO    NAME     ITEM       MONEY 001    张三        工资        80 001    张三        补贴        86 001    张三        奖金        75 002    李四        工资        78 002    李四        补贴        85 002    李四        奖金        78 求每

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

    因为要牵扯到小计,所以需要计算两次. 想法: 1.把查询到的结果,插入到临时表, 2.把统计结果插入到临时表. 3.查询临时表记录放置到游标中. 4.删除临时表记录. 包的定义声明: 复制代码 代码如下: CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IStype cursor_type is ref cursor; /*********************************************************************

  • ORACLE查询删除重复记录三种方法

    比如现在有一人员表 (表名:peosons) 若想将姓名.身份证号.住址这三个字段完全相同的记录查询出来 复制代码 代码如下: select p1.*   from persons  p1,persons  p2   where p1.id<>p2.id   and  p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address 可以实现上述效果. 几个删除重复记录的SQL语句 1.用rowid方法 2.用g

  • 查询存储过程中特定字符的方法

    把xx替换成具体字符,如"深圳",sql语句如下: 复制代码 代码如下: select * from user_source t where instr(lower(t.text),'xx')>0;select * from all_source t where t.owner<>'SYS' and instr(t.text,'××')>0; 如果是sql server数据库,可以使用如下语句: 复制代码 代码如下: select * from sysobjec

  • 关于oracle中clob字段查询慢的问题及解决方法

    最近在用oracle的过程中用到了对blob字段模糊查询的问题,对oracle来说,我并不是高手,找了很多的资料终于能够查出来了. blob字段直接用 select * from table_name where column like '%%'查找的时候是不能实现的 ,主要是字段类型不符,就想到了 字段转换成varchar2类型,然后再进行查询 select * from table_name where utl_raw.cast_to_varchar2(column) like '%orac

  • Oracle查询表里的重复数据方法

    一.背景 一张person表,有id和name的两个字段,id是唯一的不允许重复,id相同则认为是重复的记录. 二.解决 select id from group by id having count(*) > 1 按照id分组并计数,某个id号那一组的数量超过1条则认为重复. 如何查询重复的数据 select 字段1,字段2,count(*) from 表名 group by 字段1,字段2 having count(*) > 1 PS:将上面的>号改为=号就可以查询出没有重复的数据了

  • oracle查询不含括号及不含指定字符的方法

    oracle查询不含括号不含指定字符的记录方法如下: with tmp_t as( select 1 as id,'测试4321_CS' as name from dual union all select 2,'测试 1200(测试版)' from dual union all select 3,'测试123(测试版)' from dual union all select 4,'测试 1212(D2)' from dual union all select 5,'测试 1212(D2)测试版

  • python实现Oracle查询分组的方法示例

    本文实例讲述了python实现Oracle查询分组的方法.分享给大家供大家参考,具体如下: 1.分组的概念: 关键字:group by子句 结论:在select列表中如果出现了聚合函数,不是聚合函数的列,必须都要定义到group by子句的后面 需求: 查询公司各个部门的平均工资? select department_id,avg(salary) from employees group by department_id; 需求提升: 查询公司各个部门不同工种的平均工资? select depa

  • oracle查询语句大全(oracle 基本命令大全一)

    1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password//进入. 4.select table_name,column_name from user_tab_columns

  • oracle普通表转化为分区表的方法

    上一篇文章中我们了解了oracle数据与文本导入导出源码示例的相关内容,接下来我们看看,oracle中如何将普通表转化为分区表的方法. oracle官方建议当表的大小大于2GB的时候就使用分区表进行管理,分区表相对于小的表管理和性能上都有很大的优势,本文档暂时不介绍具体的优势,主要介绍几种普通表转换成分区表的方法. [方法概述]oracle官方给了以下四种操作的方法:  A)  Export/import method(导入导出)  B)  Insert with a subquery meth

  • Oracle查询语句中rownum与rowid的不同之处分析

    本文主要是以实例形式介绍了Oracle查询中rownum与rowid的不同之处,以及以假设的方式为例,查询条件为rownum = 2,在查询出第一条记录时的具体内容的介绍. 在查询中,我们可以注意到,类似于 select xx from table where rownum < n (n>1) 这样的查询是有正确含义的,而 select xx from table where rownum = n 这样的查询只在n=1的时候成立, select xx from table where rown

随机推荐