本文摘至《劍破冰山——Oracle 開發藝術》一書，喜歡的朋友可以去看一下http://www.jzxue.com/shujixiazai/shujuku/201104/02-6992.Html。
自定義聚集函數接口簡介
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 提供的聚集函數已足夠強大，想發明不重復的輪子還是很困難的。
