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 提供的***函数已足够强大，想发明不重复的轮子还是很困难的。