因为要牵扯到小计，所以需要计算两次。

想法：

1、把查询到的结果，插入到临时表，

2、把统计结果插入到临时表。

3、查询临时表记录放置到游标中。

4、删除临时表记录。

包的定义声明：

复制代码 代码如下:

CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IS
type cursor_type is ref cursor;

/************************************************************************************/







/************************************************************************************/

PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manageINVARCHAR2,
o_cursorOUTcursor_type);

END CHEN_TEST_PACKGE;

包的实现：

复制代码 代码如下:

CREATE OR REPLACE PACKAGE BODY CHEN_TEST_PACKGE IS

/************************************************************************************/







/************************************************************************************/

PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manageINVARCHAR2,
o_cursorOUTbulletin_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_Nameas bizName ,
t01as t01 ,
t07as t07 ,
t14as t14 ,
t21as t21 ,
t1Mas t1M ,
t2Mas t2M ,
t3Mas t3M ,
t4Mas t4M ,
t5Mas t5M ,
t6Mas t6M ,
t1Yas t1Y ,
t2Yas t2Y ,
tCountas tCount,
sort_noas 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配置查询结果：

复制代码 代码如下: