节选自《剑破冰山——oracle
开发艺术》一书，书籍销售地址：
http://product.china-pub.com/197199
http://product.dangdang.com/product.aspx?product_id=20981228&ref=book-02-L
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 ( ) / CREATE OR REPLACE TYPE BODY END; /
-- CREATE OR REPLACE FUNCTION f_concatenate_str(i_str / |
创建测试表和数据，并进行测试
|
CREATE TABLE TEST (ID NUMBER, NAME 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 ---------- ------------------------------------------------------------------
SQL> SELECT ----------
SQL> SELECT ----------
SQL> SELECT ---------- |
实际上在Oracle10g
版本中提供了一个未文档化的函数
wmsys.wm_concat()
，也可以实现字符串的聚集拼接；这两个函数异曲同工。
这也说明Oracle
提供的聚集函数已足够强大，想发明不重复的轮子还是很困难的。