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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    CREATE OR REPLACE TYPE typ_concatenate_impl AS OBJECT

    （

    retstr VARCHAR2（30000），      -- 拼湊使用的中間字符串

    SEPARATORFLAG  VARCHAR2（64）， -- 分隔符，默認(rèn)用自由定義| ，可以修改此處

    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ù)的功能，實(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;

    -- 用來(lái)合并兩個(gè)***函數(shù)的兩個(gè)不同的指針對(duì)應(yīng)的結(jié)果，此處默認(rèn)即可

    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)建測(cè)試表和數(shù)據(jù)，并進(jìn)行測(cè)試

    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í)行效果對(duì)照。

    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

    實(shí)際上在Oracle10g 版本中提供了一個(gè)未文檔化的函數(shù) wmsys.wm_concat（） ，也可以實(shí)現(xiàn)字符串的***拼接；這兩個(gè)函數(shù)異曲同工。

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