用Oracle用戶自定義合計函數來提高代碼重用性

2024-08-29 13:39:02

字體：大中小

來源：轉載

供稿：網友

合計函數（aggregate functions）就是對一組數據行進行處理的函數，如max和min（對一組數據取最大值和最小值）函數，合計函數用來簡化開發者執行某個任務的邏輯復雜度。從Oracle 9i開始，開發者可以創建用戶自定義的合計函數，它們可以重復使用而無需做任何修改。通過強有力的編程構造，合計函數實際上很輕易編寫。現在讓我們看看它是如何工作的。
　　
　　創建toList
　　
　　
　　為了看看構造合計函數是多么的簡單，我舉了一個合計函數的例子，即toList函數。該函數的參數為一組記錄，它的功能是把保存在這些記錄的特定字段的數據組合到一個用逗號做分隔符的清單中（成組）。下面的查詢是一個例子供你參考：
　　
　　select author, toList(title) titles
　　from books
　　group by author;
　　
　　
　　上面代碼的運行結果如圖A所示。
　　
　　圖A
　　用Oracle用戶自定義合計函數來提高代碼重用性

　　ToList函數創建了一個書目清單
　　
　　盡管我是使用Oracle的PL/SQL語言來創建我的toList函數的，你可以使用任何Oralce所支持的語言（如C＋＋或者java）來創建合計函數。所有的代碼都可以在SQL*Plus、SQL*SPReadsheet以及其它Oracle SQL應用程序中執行。
　　
　　創建一個合計函數分為五個步驟：規劃函數的邏輯、創建函數的對象類型的對象頭（object header）、編寫對象類型的代碼、例化對象類型、最后測試該函數。我將按順序向你介紹這些步驟。
　　
　　規劃邏輯
　　
　　
　　我喜歡正式規劃我所寫的程序，所以我為toList函數編寫了偽代碼，如清單A 所示，這些偽代碼就是我的邏輯規劃。Oracle實際上為我處理了該過程的相當多的部分，也就是說我實際上需要寫的代碼只包括下面三個部分：公布list變量、添加逗號的if語句以及組成返回值的字段名。
　　
　　創建對象頭（object header）
　　對象頭定義為持久變量以及對該對象成員函數的說明。所有的對象必須包含ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateMerge和ODCIAggregateTerminate這四個函數，稱之為強制函數（mandatory function），為了易讀起見，我在后面將這四個函數分別簡稱為Initialize,、Iterate、Terminate和Merge函數。在后面介紹這四個函數各自的代碼時，我再解釋它們的功能。代碼清單B 為toList的對象頭。
　　
　　在清單B中，toListImpI是對象類型的名字，而list是我所定義的唯一的持久變量。
　　
　　編寫成員函數的代碼
　　一旦創建好對象頭，你就應該編寫函數的代碼。代碼清單C 包括了toList成員函數的函數體代碼。盡管這個代碼清單有些長，但是大多數都是從一個樣板出來的，函數之間的差別不大。
　　
　　每個函數都返回一個數來表示運行結果是成功與否。ODCIConst.sUCcess表示運行成功?，F在你已經看到了這些函數的代碼，那么四個強制函數中的三個的功能應該很輕易理解了：
　　
　　Initialize函數對數據組各個需要處理的字段各運行一次。自然的，我需要為每一個值預備一個新的清單，所以需要初始化持久變量list，這里初始化值為null。
　　Iterate函數處理返回的行，所以實際上是由它來創建返回的值的清單。先測試list是否為空，假如為空，就把list直接設置為所引入的value值；假如list變量非空，則給list添加一個逗號后再插入value值。
　　Terminate函數在數據組的每個行的感愛好字段數據被處理后執行。在這個函數中我只需簡單的返回清單變量即可。
　　
　　第四個函數，即Merge函數，僅僅是用來返回成功標記的。
　　
　　創建該合計函數
　　一旦你寫完所有的這些代碼，剩下的唯一事情就是通過運行下面的指令來創建該合計函數（這用到你剛剛創建的toListImp1類型）：
　　
　　create or replace function toList (input STRING) return STRING
　　aggregate USING toListImp1;
　　
　　
　　現在開始進入最有趣的工作環節：測試。你可以用清單D 中的代碼創建一個簡單的書目表并載入一些數據進行測試。
　　
　　使函數具有并行處理能力
　　
　　
　　合計函數的另一個優點就是你可以讓它們具有Oracle的并行處理特性。為了讓你的合計函數具有并行處理能力，你需要編寫Merge函數的代碼并需要對合計函數的腳本略做修改。注重我已經給你的代碼是正確完整的、并帶有并行處理選項。
　　
　　當一個合計函數在多個處理器上運行時，Oracle 9i服務器可以在兩個或者多個處理器上運行Iterate函數。每個處理器返回一部分結果，Merge函數把這些離散的結果合并到一個變量。Oracle 9i服務器將反復調用Merge函數直到所有的部分結果合并完畢為止。

　　
　　為了讓toList函數可以并行處理，你首先需要Merge函數的代碼，你可以在清單E中找到它的代碼。
　　
　　我還需要在創建該合計函數時公布它具有并行處理的能力：
　　
　　create or replace function toList (input STRING) return STRING
　　parallel_enable　aggregate USING toListImp1;
　　
　　現在，toList函數已經充分利用了多處理器的優點了。
　　
　　使用技巧
　　假如你不想按字段成組，那么可以按常量進行，例如：
　　
　　Select toList(distinct Author) authors From books Group by ‘1’
　　
　　你也許希望增強你的合計函數的健壯性，就像在其它程序中所作的那樣。例如，在我的Merge函數中，也許應該檢測合并的兩個內容是否超過了list的最大答應字符數（32767）。
　　
　　假如你的Oracle服務器沒有配置成支持并行處理的方式，我建議你仍在你的合計函數中考慮添加并行支持功能。因為這只需花費一點點附加時間，但是這就增加了你的函數在其它處理環境中的可移植性和可重用性。
　　
　　最后你還需要注重，Oracle 9i將會答應你創建一些根本就不能運行的合計函數。例如，盡管Oracle答應你創建參數超過一個的合計函數（也就是說，該合計函數將使用多個函數），但是使用該合計函數將會發生錯誤。同樣的，盡管你可以重載合計函數，但是當你嘗試使用它們的時候卻會導致失敗。
　　
　　創建自己的合計函數是擴展Oracle統計和文本處理例程（Oracle本身并沒有內建這個功能）的重要途徑。使用這個技術的地方幾乎數不勝數，所以你需要把握這個技術。

上一篇：Oracle9i 對象關系技術

下一篇：關于Oracle for zLinux的幾個問題