首页 > 数据库 > oracle

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

admin oracle 2022-02-09 22:37:37 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配置查询结果:

复制代码 代码如下:


    
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
   

       
   
        
        
    
    
   
    
        {call CHEN_TEST_PACKGE.p_list_bulletin_report(?,?)}
   

java调用:

复制代码 代码如下:
String id_capital_dynamic_manage = request.getParameter("id_capital_dynamic_manage");
            Map paraMap = new HashMap();
            paraMap.put("i_id_capital_dynamic_manage", id_capital_dynamic_manage);
            // 调用存储过程,查询
            List resultList = (List<?>) CURDUtil.queryList("queryBulletinReportList", paraMap);

页面显示jsp:

复制代码 代码如下:

                 
                 
                   
                   
                   
                   
                   
                   
                   
                   
                   
                 
                 

             

版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:/shujuku/oracle/99875.html

留言与评论(共有 0 条评论)
   
验证码:

潘少俊衡

| 桂ICP备2023010378号-4

Powered By EmpireCMS

爱享小站

中德益农

谷姐神农

环亚肥料

使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

感谢潘少俊衡友情技术支持