Oracle自定义聚集函数 收藏 此文于2011-04-01被推荐到CSDN首页
如何被推荐？
节选自《剑破冰山——oracle 开发艺术》一书，书籍销售地址：ITPUB个人空间)up/K(j8t Jx|O"~5J
http://product.china-pub.com/197199ITPUB个人空间s] c3^'dw5}T.M6W
http://product.dangdang.com/product.aspx?product_id=20981228&ref=book-02-L
n ~Cw q&{+E6517 http://www.amazon.cn/mn/detailApp?uid=477-1785695-2022958&ref=RK&asin=B004CMIVM6
自定义聚集函数
自定义聚集函数接口简介
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 提供的聚集函数已足够强大，想发明不重复的轮子还是很困难的
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/baoqiangwang/archive/2011/03/31/6293004.aspx