在ORACLE中實現類似EXCEL的復雜報表

摘要:報表在我們日常工作中及其重要，特別是象公安等部門更需要各種報表，以掌握關心的數據。然而，中國式報表比較復雜，給信息化應用系統開發帶來一定的困難。文章利用ORACLE的最新技術特性，以存儲過程的方式實現了類似EXCEL單元格計算的功能，滿足了對統計結果的部分行進行運算的要求。

關鍵詞: ORACLE;EXCEL;報表

中圖法分類號:TP311.52 文獻標識碼:A文章編號:1009-3044(2009)33-9572-02

The Implementation of Complex Report Like EXCEL in ORACLE

YANG Ling-xing

(Math and Information Science Institute in Qujing Nomal College， Qujing 655011， China)

Abstract: Report is important in our life， especially such as the police departments need all kinds of reports in order to master the concerned data. However， Chinese-style report is so complex that brings a certain degree of difficulty to the information-based application development. The article uses the ORACLE latest technology to realize the function of the cell calculation by store procedure form，to meet some of the statistical results of the requirements of row operations.

Key words: oracle; excel; report

1 概述

報表統計一直是各單位和部門信息化建設中的重要部分，能夠根據客戶需要定制各種報表已經成為開發商有力的競爭條件。因此，很多公司都研制了自己的報表組件，象華表CELL以及國外的FarPoint等都是優秀的報表控件。利用它們能開發出很實用的各種中國式報表。

以往利用報表控件或者其他方式來生成如表1(這里的報表不是完整的，僅僅為了說明問題而舉例，第二行的數據是三到四行的合計，第五行的數據是六到七行的合計)所示的報表，一般的做法會是返回相關數據，然后在客戶端做必需的運算，而且需要商業報表控件的支撐。但現在也可以采取另一種方式，利用ORACLE存儲過程能在后臺直接生成需要的數據，返回到客戶端只做需簡單顯示，實現起來也不難，對于報表復雜和種類繁多的系統非常實用。下面我們就來闡述該方法的實現過程，再做詳細說明之前，先簡單介紹ORACLE中用到的相關函數。

2 ORACLE相關技術介紹

ORACLE從9i開始就提供了函數可以實現查詢結果的行間計算，到10g逐漸完善，實現該功能的語句是MODEL從句，該從句是ORACLE為企業智能領域引入的一個重要的新功能。MODEL[1]從句的目的就是讓SQL語句擁有從普通的SELECT結果中創建多維數組的能力，然后再在這個SQL電子表格上進行行間或數組的計算。MODEL從句通過將一個查詢中的各列映射為三組而定義一個多維數組，這三個組分別是分區(partitions)、維度(dimensions)和計量(measures)。分區定義的是結果集中作為獨立數組的邏輯塊，緯度定義的是分區中每個計量單元，計量則是實際的數據單元。計量一般會包括象銷售量或者營業收入等數值。我們可以通過指定完全的維度組合來訪問分區中的每個計量單元。

下面是MODEL從句的最基本語法，MODEL從句很復雜，要想了解更多，可以參考ORACLE 10G SQL REFERENCE MANUAL。

MODEL [RETURN [UPDATED | ALL] ROWS]

[reference models]

[PARTITION BY ()]

DIMENSION BY ()

MEASURES () [IGNORE NAV] | [KEEP NAV]

[RULES

[UPSERT | UPDATE]

[AUTOMATIC ORDER | SEQUENTIAL ORDER]

[ITERATE (n) [UNTIL ] ]

( = ... )

有了這種新功能，我們就能根據實際情況來實現類似EXCEL電子表格那樣的進行單元格的運算，也就能實現滿足我們常見報表的需求了，下面是實現表1樣式的報表的方法闡述。

3 實現方法

對于統計報表的其他部分，與通常的存儲過程并無差別，在此就不重復介紹了，現在重點介紹實現象表1中那樣第二行的小記的數據是三到四行的合計的存儲過程方法，該存儲過程的主要內容是:

Procedure GetReport(out_records recores out)

Begin

Open out_rc For

Select row_id， row_name，

decode(slhj， 0， Null， slhj)slhj，

……

decode(blnq ， 0， Null， blnq )blnq ，

decode(saje， 0， Null， saje)saje

From ( Select '1' c1， report_id， row_id， report_name， row_name

From gab_report_row left )left join a ...left join b ......

--和業務表關聯，詳細省略

--生成合計與小計行

Model

Partition By (c1)

Dimension By (a.row_id)

Measures (a.row_name row_name，

b.slhj slhj， b.slzjsl slzjsl， b.slbmys slbmys，

b.dyaj dyaj， b.nnyq nnyq， b.jq jq ，

b.xz xz)

(row_name[1] = '偽劣商品-小計'，

slhj[2]=sum(slhj)[row_id Between 3 And 4]，

slzjsl[2]=sum(slzjsl)[row_id Between 3 And 4]，

slbmys[2]=sum(slbmys)[row_id Between 3 And 4]，

……

row_name[5] = '妨害公司、企業管理-小計'，

slhj[5]=sum(slhj)[row_id Between 6 And 7]，

slzjsl[5]=sum(slzjsl)[row_id Between 6 And 7]，

slbmys[5]=sum(slbmys)[row_id Between 6 And 7]，

……

dwz)

Order By row_id;

End;

上面的存儲過程中，實現對行進行運算的部分主要是，把查詢結果分成一個一維數組，用返回的行號作為索引，返回的列作為計量，實現如表1的報表的行間小記功能。

4 結束語

上面簡單介紹了用存儲過程的方式來實現復雜報表的方法，由于運算主要在數據庫一端完成，所以對數據庫服務器的性能要求比較高，而采用其他方法來實現相同的報表功能要求在客戶端運算，對運算量不大的報表可以接受，而對大數據量的運算則要權衡一下了。目前數據庫服務器和WEB服務器的硬件性能要求已經不是主要問題，這里介紹的方法應該可以用在大多數系統中。當然，選擇何種方式來實現還有根據具體情況而定。

參考文獻:

[1] 《Sybex Oracle 10g 新特性學習筆記》——安全性和SQL功能增強[EB/OL].http://turner.itpub.net/post/2343/67194.

[2] 王海亮，林立新，于三祿，等.精通Oracle 10g PL/SQL編程[M].北京:中國水利水電出版社，2004.

[3] 孫風棟.Oracle 10g數據庫基礎教程[M].北京:電子工業出版社，2009.