Oracle自定義聚集函式
節選自《劍破冰山——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提供的聚集函式已足夠強大,想發明不重複的輪子還是很困難的。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6517/viewspace-691426/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 自定義函式Oracle函式
- Oracle優化案例-自定義函式索引(五)Oracle優化函式索引
- shell自定義函式函式
- Hive常用函式及自定義函式Hive函式
- hive 3.0.0自定義函式Hive函式
- Hive中自定義函式Hive函式
- python教程:自定義函式Python函式
- java自定義equals函式和hashCode函式Java函式
- PHP 自定義函式用法及常用函式集合PHP函式
- Hive函式(內建函式+自定義標準函式UDF)Hive函式
- Laravel 新增自定義助手函式Laravel函式
- laravel 自定義全域性函式Laravel函式
- Laravel 自定義函式存放位置Laravel函式
- Laravel自定義輔助函式Laravel函式
- FlinkSQL自定義函式開發SQL函式
- MySQL使用之五_自定義函式和自定義過程MySql函式
- JavaScript 設計模式系列 – 自定義函式(惰性函式)JavaScript設計模式函式
- Ignite自定義函式注意事項函式
- VBA 自定義常用函式 (備用)函式
- php自定義函式放哪兒PHP函式
- matlab自定義函式建立與使用Matlab函式
- MySQL全面瓦解18:自定義函式MySql函式
- 【TUNE_ORACLE】列出帶有自定義函式的SQL的SQL參考Oracle函式SQL
- Apache Phoenix自定義函式(UDF)實踐Apache函式
- sql中select列有自定義函式 dblinkSQL函式
- Qt自定義動畫插值函式QT動畫函式
- 單據列表呼叫自定義SQL函式SQL函式
- HIVE自定義函式的擴充套件Hive函式套件
- Excel vba自定義函式公式智慧提示Excel函式公式
- 22.python自定義函式(format,zip)Python函式ORM
- Clickhouse 使用者自定義外部函式函式
- 【vue】自定義一個websocket工具函式VueWeb函式
- 皕傑報表之自定義函式函式
- Hive--->建立自定義的UDTF函式Hive函式
- 動畫函式的繪製及自定義動畫函式動畫函式
- 自定義生成器函式模擬Python內建函式filter()函式PythonFilter
- MySQL自定義函式與儲存過程MySql函式儲存過程
- SQL優化案例-自定義函式索引(五)SQL優化函式索引
- T-SQL——自定義函式解析JSON字串SQL函式JSON字串