转载

自定义聚集函数

自定义聚集函数接口简介

Oracle提供了很多预定义好的聚集函数,比如Max(), Sum(), AVG(), 但是这些预定义的聚集函数基本上都是适应于标量数据(scalar data), 对于复杂的数据类型,比如说用户自定义的Objecttype, Clob等, 是不支持的。

但是,幸运的是, 用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregateinterface来创建自定义聚集函数,而且自定义的聚集函数跟内建的聚集函数用法上没有差别。

通过实现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(sctxIN OUTstring_agg_type ) return number

  自定义聚集函数初始化操作,从这儿开始一个聚集函数。初始化的聚集环境(aggregation context)会以对象实例(object typeinstance)传回给oracle.

b. member function ODCIAggregateIterate(selfIN OUT string_agg_type ,value IN varchar2) return number

  自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self为当前聚集函数的指针,用来与前面的计算结果进行关联

  这个函数用来遍历需要处理的数据,被oracle重复调用。每次调用的时候,当前的aggreation context和 新的(一组)值会作为传入参数。 这个函数会处理这些传入值,然后返回更新后的aggregation context.这个函数对每一个NON-NULL的值都会被执行一次。NULL值不会被传递个聚集函数。

c. member function ODCIAggregateMerge (selfIN string_agg_type,returnValue OUT varchar2,flags IN number) return number

  用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

  这个函数用来把两个aggregation context整合在一起,一般用来并行计算中(当一个函数被设置成enable parallel处理的时候)。

d. member functionOCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flagsIN number)

   终止聚集函数的处理,返回聚集函数处理的结果.

  这个函数是Oracle调用的最后一个函数。它接收aggregation context作为参数,返回最后的aggregatevalue.  

应用场景一:字符串聚集

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提供的聚集函数已足够强大,想发明不重复的轮子还是很困难的。
文章最后发布于: 2011-09-26 17:09:35
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览