導讀:1建站知識Oracle 查詢存儲過程做橫向報表的方法，需要的朋友可以參考一下建設網站公司seo網站排名優化軟件。

因為要牽扯到小計，所以需要計算兩次。

想法：

1、把查詢到的結果，插入到臨時表，

2、把統計結果插入到臨時表。

3、查建設網站公司詢臨時表記錄放置到游標中。

4、刪除臨時表記錄。

包的定義聲明：

復制代碼 代碼如下:

CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IStype 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網站seo優化軟件 , 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 , 網站seo優化培訓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;/

關鍵詞標簽: 橫向 報表