節選自《劍破冰山——oracle開發藝術》一書，書籍銷售地址：
http://product.china-pub.com/197199
http://product.dangdang.com/product.aspx?product_id=20981228&ref=book-02-L
http://www.amazon.cn/mn/detailApp?uid=477-1785695-2022958&ref=RK&asin=B004CMIVM6
自定義聚集函式介面簡介
Oracle提供了很多預定義好的聚集函式，比如Max(), Sum(), AVG()， 但是這些預定義的聚集函式基本上都是適應於標量資料(scalar data)， 對於複雜的資料型別，比如說使用者自定義的Object type, Clob等， 是不支援的。
但是，幸運的是， 使用者可以通過實現Oracle的Extensibility Framework中的ODCIAggregate interface來建立自定義聚集函式，而且自定義的聚集函式跟內建的聚集函式用法上沒有差別。
通過實現ODCIAggregate rountines來建立自定義的聚集函式。可以通過定義一個物件型別（Object Type），然後在這個型別內部實現ODCIAggregate介面函式(routines)， 可以用任何一種Oracle支援的語言來實現這些介面函式，比如C/C++, JAVA, PL/SQL等。在這個Object Type定義之後，相應的介面函式也都在該Object Type Body內部實現之後， 就可以通過CREATE FUNCTION語句來建立自定義的聚集函式了。
每個自定義的聚集函式需要實現4個ODCIAggregate介面函式， 這些函式定義了任何一個聚集函式內部需要實現的操作，這些函式分別是initialization, iteration, merging和termination。
a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type ) return number
自定義聚集函式初始化操作,從這兒開始一個聚集函式。初始化的聚集環境(aggregation context)會以物件例項(object type instance)傳回給oracle.
b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2) return number
自定義聚集函式,最主要的步驟,這個函式定義我們的聚集函式具體做什麼操作,後面的例子,是取最大值,最小值,平均值,還是做連線操作.self為當前聚集函式的指標,用來與前面的計算結果進行關聯
這個函式用來遍歷需要處理的資料，被oracle重複呼叫。每次呼叫的時候，當前的aggreation context和 新的（一組）值會作為傳入引數。 這個函式會處理這些傳入值，然後返回更新後的aggregation context.這個函式對每一個NON-NULL的值都會被執行一次。NULL值不會被傳遞個聚集函式。
c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number
用來合併兩個聚集函式的兩個不同的指標對應的結果,使用者合併不同結果結的資料,特別是處理並行(parallel)查詢聚集函式的時候.
這個函式用來把兩個aggregation context整合在一起，一般用來平行計算中（當一個函式被設定成enable parallel處理的時候）。
d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)
終止聚集函式的處理,返回聚集函式處理的結果.
這個函式是Oracle呼叫的最後一個函式。它接收aggregation context作為引數，返回最後的aggregate value.
應用場景一：字串聚集
|
CREATE OR REPLACE TYPE typ_concatenate_impl AS OBJECT ( retstr VARCHAR2(30000), —拼湊使用的中間字串 SEPARATORFLAG VARCHAR2(64), —分隔符，預設用自由定義|，可以修改此處 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(sctx IN OUT typ_concatenate_impl) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT typ_concatenate_impl, value IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN typ_concatenate_impl, returnvalue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER ) / CREATE OR REPLACE TYPE BODY typ_concatenate_impl IS —自定義聚集函式初始化操作 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(sctx IN OUT typ_concatenate_impl) RETURN NUMBER IS BEGIN sctx := typ_concatenate_impl(“,`,`); RETURN ODCICONST.SUCCESS; END; —定義函式的功能，實現字串拼接 MEMBER FUNCTION ODCIAGGREGATEITERATE(self IN OUT typ_concatenate_impl, value IN VARCHAR2) RETURN NUMBER IS BEGIN self.retstr := self.retstr || value||self.SEPARATORFLAG; RETURN ODCICONST.SUCCESS; END; —定義終止聚集函式的處理,返回聚集函式處理的結果 MEMBER FUNCTION ODCIAGGREGATETERMINATE(self IN typ_concatenate_impl, returnvalue OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN IF returnvalue IS NOT NULL THEN returnvalue := SUBSTR(self.retstr,1,LENGTH(self.retstr)-1); ELSE returnvalue := self.retstr; END IF; RETURN ODCICONST.SUCCESS; END; —用來合併兩個聚集函式的兩個不同的指標對應的結果，此處預設即可 MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER IS BEGIN RETURN ODCICONST.SUCCESS; END; END; /
—建立自定義函式 CREATE OR REPLACE FUNCTION f_concatenate_str(i_str VARCHAR2) RETURN VARCHAR2 AGGREGATE USING typ_concatenate_impl; / |
建立測試表和資料，並進行測試
|
CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20)); INSERT INTO TEST VALUES (1, `AAA`); INSERT INTO TEST VALUES (2, `BBB`); INSERT INTO TEST VALUES (1, `ABC`); INSERT INTO TEST VALUES (3, `CCC`); INSERT INTO TEST VALUES (2, `DDD`); COMMIT; |
檢視執行後的結果，並與WMSYS.WM_CONCAT函式執行效果對照。
|
SQL> SELECT id,f_concatenate_str(name) name FROM test GROUP BY id; ID NAME ———- —————————————————————— 1 AAA,ABC, 2 BBB,DDD, 3 CCC,
SQL> SELECT id,wmsys.wm_concat(name) name FROM test GROUP BY id; ID NAME ———- —————————————————————— 1 AAA,ABC 2 BBB,DDD 3 CCC
SQL> SELECT id,f_concatenate_str(name) OVER (PARTITION BY id) name FROM test; ID NAME ———- —————————————————————— 1 AAA,ABC, 1 AAA,ABC, 2 DDD,BBB, 2 DDD,BBB, 3 CCC,
SQL> SELECT id,wmsys.wm_concat(name) OVER (PARTITION BY id) name FROM test; ID NAME ———- —————————————————————— 1 AAA,ABC 1 AAA,ABC 2 DDD,BBB 2 DDD,BBB 3 CCC |
實際上在Oracle10g版本中提供了一個未文件化的函式wmsys.wm_concat()，也可以實現字串的聚集拼接；這兩個函式異曲同工。
這也說明Oracle提供的聚集函式已足夠強大，想發明不重複的輪子還是很困難的。