Oracle自訂聚集合函式

標籤：

今天工作中看見別人寫的自訂聚集合函式，所以一門心思的想搞懂，就在網上找資料了。自訂聚集合函式自訂聚集合函式介面簡介

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提供的聚集合函式已足夠強大，想發明不重複的輪子還是很困難的。

Oracle自訂聚集合函式