在ORACLE中實現(xiàn)類似EXCEL的復雜報表

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

關鍵詞: 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 概述

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

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

2 ORACLE相關技術介紹

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

下面是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 ] ]

( = ... )

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

3 實現(xiàn)方法

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

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 ......

--和業(yè)務表關聯(lián)，詳細省略

--生成合計與小計行

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] = '妨害公司、企業(yè)管理-小計'，

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;

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

4 結束語

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

參考文獻:

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

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

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