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

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>