Oracle自定义聚集函数

在yangtingkun博客上看到很早以前的一篇文章，是用一个type来自定义聚集函数的，觉得非常神奇，想想用普通的函数定义还真的是不能满足需求，只能用Oracle的说明来做。开始死活看不懂这个type定义的原理，发现参数传入传出的好麻烦，后来在网上找了Oracle的文档，发现这是Oracle规定的固定格式，而且函数名也是确定的，貌似这样才能依次调用。下面简单了解一下：

关于函数的介绍看这里：http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciaggref.htm#BABCFHFB

具体的介绍看这里：http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dciaggfns.htm

总体感觉就是一个固定模式的套用，只要简单记一下用法就可以了。而且Oracle文档上也没有太具体的描述。看个例子(转载)：

对字符串的“sum”——在Oracle中自定义聚集函数的例子
===========================================================
作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2004.10.19 17:59
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/3380
---------------------------------------------------------------
Oracle的定制功能十分强大。前几天在碰到一个问题的时候，查询文档发现，oracle不但允许用户定制自己的函数，还可以定制自己的聚集函数和分析函数。

下面是我使用自定义聚集函数建立一个字符串“sum”的小例子。

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
2 STR VARCHAR2(30000),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
7 )
8 /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE;
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SELF.STR;
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /

类型主体已创建。

SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
2 AGGREGATE USING T_LINK;
3 /

函数已创建。

SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(20));

表已创建。

SQL> INSERT INTO TEST VALUES (1, 'AAA');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (2, 'BBB');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (1, 'ABC');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (3, 'CCC');

已创建 1 行。

SQL> INSERT INTO TEST VALUES (2, 'DDD');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> COL NAME FORMAT A60

SQL> SELECT ID, F_LINK(NAME) NAME FROM TEST GROUP BY ID;

ID NAME
---------- ------------------------------------------------------
1 AAAABC
2 BBBDDD
3 CCC

附：如果关于type的一些基础用法，可以参见此处：http://blog.chinaunix.net/u/20762/showart_329455.html