频道栏目
首页 > 数据库 > Oracle > 正文
Oracle自定义聚集函数
2011-05-26 14:43:45           
收藏   我要投稿

自定义聚集函数
自定义聚集函数接口简介
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 te

点击复制链接 与好友分享!回本站首页
相关TAG标签 自定 函数
上一篇:Oracle数据库实体类生成工具For Winston
下一篇:64位Win7下32位Oracle客户端的使用
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站