用一个实例讲解Oracle的自定义聚集函数

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 NUMBE

R) 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 NUMBE

R) 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