自定义分析函数

from:http://yangtingkun.itpub.net/

 

Oracle在文档中明确说明支持自定义聚集函数和分析函数。但是我只看到聚集函数的例子。我在这篇文章中介绍了自定义聚集参数：http://blog.itpub.net/post/468/3380

 

今天在论坛里看到有人提出，由于对字符串合并操作和对数值的SUM操作并不完全一样。数值的sum满足交换率，也就是说，先后顺序不会影响到最终的查询结果，但是字符串的合并结果是和数据的顺序是有关的。

 

于是就提出能否按照字符串本身的排序来合并，原文见：http://www.itpub.net/338337.html

 

 

其实这个需求利用分析函数很容易实现，而我通过测试发现，居然自定义的聚集函数也可以当做分析函数来使用。

 

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

 

 

 

表已创建。

 

 

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 := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
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> COL OWNER FORMAT A8
SQL> COL LINK_ID FORMAT A60
SQL> SELECT OWNER, F_LINK(ID) LINK_ID FROM T WHERE OWNER = 'SCOTT' GROUP BY OWNER;

 

 

 

OWNER LINK_ID
-------- -----------------------------------------------------------
SCOTT 32488,32489,32490,32492,32494,32501,32502,32495,32493,32491

 

 

 

目前聚集函数并没有按照ID的顺序排列。

 

SQL> SELECT OWNER, F_LINK(ID) OVER (ORDER BY ID) LINK_ID FROM T WHERE OWNER = 'SCOTT';

 

 

 

OWNER LINK_ID
-------- ------------------------------------------------------------
SCOTT 32488
SCOTT 32488,32489
SCOTT 32488,32489,32490
SCOTT 32488,32489,32490,32491
SCOTT 32488,32489,32490,32491,32492
SCOTT 32488,32489,32490,32491,32492,32493
SCOTT 32488,32489,32490,32491,32492,32493,32494
SCOTT 32488,32489,32490,32491,32492,32493,32494,32495
SCOTT 32488,32489,32490,32491,32492,32493,32494,32495,32501
SCOTT 32488,32489,32490,32491,32492,32493,32494,32495,32501,32502

 

 

 

已选择

10行。

 

 

可以看到，自定义的聚集函数也可以当作分析函数来使用。

 

下面简单的取最大值就可以得到相应的结果。

 

SQL> SELECT OWNER, MAX(LINK_ID) LINK_ID FROM

 

 

2 (SELECT OWNER, F_LINK(ID) OVER (ORDER BY ID) LINK_ID
3 FROM T WHERE OWNER = 'SCOTT')
4 GROUP BY OWNER;

 

OWNER LINK_ID
-------- ------------------------------------------------------------
SCOTT 32488,32489,32490,32491,32492,32493,32494,32495,32501,32502

 

 

 

SQL> SELECT OWNER, MAX(LINK_ID) LINK_ID FROM
2 (SELECT OWNER, F_LINK(ID) OVER (ORDER BY ID DESC) LINK_ID
3 FROM T WHERE OWNER = 'SCOTT')
4 GROUP BY OWNER;

 

 

 

OWNER LINK_ID
-------- ------------------------------------------------------------
SCOTT 32502,32501,32495,32494,32493,32492,32491,32490,32489,32488