Oracle自定義***函數(shù)

 Oracle 提供了很多預(yù)定義好的***函數(shù)，比如Max（）， Sum（）， AVG（） ， 但是這些預(yù)定義的***函數(shù)基本上都是適應(yīng)于標量數(shù)據(jù)（scalar data ） ， 對于復雜的數(shù)據(jù)類型，比如說用戶自定義的Object type, Clob 等， 是不支持的。

    但是，幸運的是， 用戶可以通過實現(xiàn)Oracle 的Extensibility Framework 中的ODCIAggregate interface 來創(chuàng)建自定義***函數(shù)，而且自定義的***函數(shù)跟內(nèi)建的***函數(shù)用法上沒有差別。

    通過實現(xiàn)ODCIAggregate rountines 來創(chuàng)建自定義的***函數(shù)�？梢酝ㄟ^定義一個對象類型（Object Type ），然后在這個類型內(nèi)部實現(xiàn)ODCIAggregate 接口函數(shù)（routines） ， 可以用任何一種Oracle 支持的語言來實現(xiàn)這些接口函數(shù)，比如C/C++, JAVA, PL/SQL 等。在這個Object Type 定義之后，相應(yīng)的接口函數(shù)也都在該Object Type Body 內(nèi)部實現(xiàn)之后， 就可以通過CREATE FUNCTION 語句來創(chuàng)建自定義的***函數(shù)了。

    每個自定義的***函數(shù)需要實現(xiàn)4 個ODCIAggregate 接口函數(shù)， 這些函數(shù)定義了任何一個***函數(shù)內(nèi)部需要實現(xiàn)的操作，這些函數(shù)分別是initialization, iteration, merging 和termination 。

    a. static function ODCIAggregateInitialize（sctx IN OUTstring_agg_type ） return number

    自定義***函數(shù)初始化操作， 從這兒開始一個***函數(shù)。初始化的***環(huán)境（aggregation context） 會以對象實例（object type instance） 傳回給oracle.

    b. member function ODCIAggregateIterate（self IN OUT string_agg_type ,value IN varchar2） return number

    自定義***函數(shù)， 最主要的步驟， 這個函數(shù)定義我們的***函數(shù)具體做什么操作， 后面的例子， 是取最大值， 最小值， 平均值， 還是做連接操作。self 為當前***函數(shù)的指針， 用來與前面的計算結(jié)果進行關(guān)聯(lián)

    這個函數(shù)用來遍歷需要處理的數(shù)據(jù)，被oracle 重復調(diào)用。每次調(diào)用的時候，當前的aggreation context 和 新的（一組）值會作為傳入?yún)��?shù)。 這個函數(shù)會處理這些傳入值，然后返回更新后的aggregation context. 這個函數(shù)對每一個NON-NULL 的值都會被執(zhí)行一次。NULL 值不會被傳遞個***函數(shù)。

    c. member function ODCIAggregateMerge （self IN string_agg_type,returnValue OUT  varchar2,flags IN number） return number

    用來合并兩個***函數(shù)的兩個不同的指針對應(yīng)的結(jié)果， 用戶合并不同結(jié)果結(jié)的數(shù)據(jù)， 特別是處理并行（parallel） 查詢***函數(shù)的時候。

    這個函數(shù)用來把兩個aggregation context 整合在一起，一般用來并行計算中（當一個函數(shù)被設(shè)置成enable parallel 處理的時候）。

    d. member function OCDIAggregateTerminate（self IN string_agg_type,returnValue OUT varchar2,flags IN number）

    終止***函數(shù)的處理， 返回***函數(shù)處理的結(jié)果。

    這個函數(shù)是Oracle 調(diào)用的最后一個函數(shù)。它接收aggregation context 作為參數(shù)，返回最后的aggregate value.

    應(yīng)用場景一：字符串***

    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

    -- 自定義***函數(shù)初始化操作

    STATIC FUNCTION ODCIAGGREGATEINITIALIZE（sctx IN OUT typ_concatenate_impl） RETURN NUMBER IS

    BEGIN

    sctx := typ_concatenate_impl（'',','）；

    RETURN ODCICONST.SUCCESS;

    END;

    -- 定義函數(shù)的功能，實現(xiàn)字符串拼接

    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;

    -- 定義終止***函數(shù)的處理， 返回***函數(shù)處理的結(jié)果

    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;

    -- 用來合并兩個***函數(shù)的兩個不同的指針對應(yīng)的結(jié)果，此處默認即可

    MEMBER FUNCTION ODCIAGGREGATEMERGE（self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl） RETURN NUMBER IS

    BEGIN

    RETURN ODCICONST.SUCCESS;

    END;

    END;

    /

    -- 創(chuàng)建自定義函數(shù)

    CREATE OR REPLACE FUNCTION f_concatenate_str（i_str VARCHAR2） RETURN VARCHAR2

    AGGREGATE USING typ_concatenate_impl;

    /

    創(chuàng)建測試表和數(shù)據(jù)，并進行測試

    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;

    查看執(zhí)行后的結(jié)果，并與WMSYS.WM_CONCAT 函數(shù)執(zhí)行效果對照。

    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 版本中提供了一個未文檔化的函數(shù) wmsys.wm_concat（） ，也可以實現(xiàn)字符串的***拼接；這兩個函數(shù)異曲同工。

    這也說明Oracle 提供的***函數(shù)已足夠強大，想發(fā)明不重復的輪子還是很困難的