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

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,

(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,'biz_id_count' as biz_id,

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;

/