oracle自定义聚集函数

oracle自定义聚集函数采用oracle提供的接口，是一个固定的模式。要定义oracle聚集函数需要定义一下4个函数。

a：STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )
RETURN NUMBER
自定义聚集函数初始化设置，从这儿开始一个聚集函数。
b：MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT STR_CONN_TYPE ,VALUE IN NUMBER )
RETURN NUMBER
自定义聚集函数最主要的步骤，这个函数定义我们的聚集函数具体做什么操作，后面的例子。是取最大值、最小值、平均值、还是做连接操作。self为当前聚集函数的指针，用来与前面的计算结果进行关联。
c：MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT STR_CONN_TYPE,ctx2 IN STR_CONN_TYPE)
RETURN NUMBER,
用来合并两个聚集函数的两个不同的指针对应的结果，用户合并不同结果结的数据，特别是处理并行(parallel)查询聚集函数的时候。
d：MEMBER FUNCTION ODCIAggregateTerminate(SELF IN STR_CONN_TYPE,returnValue OUT VARCHAR2,flags IN NUMBER)
终止聚集函数的处理，返回聚集函数处理的结果。

---

例子：

CREATE OR REPLACE TYPE STR_CONN_TYPE AS OBJECT

(

vStr VARCHAR2 ( 4000 ),

STATIC FUNCTION

ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE )

RETURN NUMBER ,

MEMBER FUNCTION

ODCIAggregateIterate( SELF IN OUT STR_CONN_TYPE ,

VALUE IN NUMBER )

RETURN NUMBER ,

MEMBER FUNCTION

ODCIAggregateMerge( SELF IN OUT STR_CONN_TYPE,

ctx2 IN STR_CONN_TYPE)

RETURN NUMBER ,

MEMBER FUNCTION

ODCIAggregateTerminate( SELF IN STR_CONN_TYPE,

returnValue OUT VARCHAR2 ,

flags IN NUMBER )

RETURN NUMBER

);

/

CREATE OR REPLACE TYPE BODY STR_CONN_TYPE

IS

STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT STR_CONN_TYPE)

RETURN NUMBER

IS

BEGIN

sctx := STR_CONN_TYPE( NULL );

RETURN ODCIConst.Success;

END ;

MEMBER FUNCTION ODCIAggregateIterate( SELF IN OUT STR_CONN_TYPE,

VALUE IN NUMBER )

RETURN NUMBER

IS

BEGIN

SELF.vStr := SELF.vStr || ';' || VALUE ;

RETURN ODCIConst.Success;

END ;

MEMBER FUNCTION ODCIAggregateMerge( SELF IN OUT STR_CONN_TYPE,

ctx2 IN STR_CONN_TYPE)

RETURN NUMBER

IS

BEGIN

RETURN ODCIConst.Success;

END ;

MEMBER FUNCTION ODCIAggregateTerminate( SELF IN STR_CONN_TYPE,

returnValue OUT VARCHAR2 ,

flags IN NUMBER )

RETURN NUMBER

IS

BEGIN

returnValue := LTRIM (SELF.vStr, ';' );

RETURN ODCIConst.Success;

END ;

END ;

/

CREATE OR REPLACE FUNCTION ConnStr(input VARCHAR2 )

RETURN VARCHAR2

PARALLEL_ENABLE AGGREGATE USING STR_CONN_TYPE;

/

---

应用

SELECT * FROM t_Ssq_Bqhm t ORDER BY qh,hmlx,hm

QH HM HMLX
1 05021 1 0
2 05021 2 0
3 05021 3 0
4 05021 4 0
5 05021 5 0
6 05021 6 0
7 05021 7 0
8 05021 8 0
9 05021 9 0
10 05021 1 1
11 05021 12 1
12 05028 2 0
13 05028 6 0
14 05028 7 0
15 05028 8 0
16 05028 18 0
17 05028 19 0
18 05028 20 0
19 05028 23 0
20 05028 28 0
21 05028 29 0
22 05028 30 0
23 05028 2 1
24 05028 9 1
25 05028 10 1
26 05028 11 1

SELECT qh,hmlx,connstr(hm) AS HMS FROM t_ssq_bqhm t GROUP BY qh,hmlx

QH HMLX HMS
1 05021 0 1;2;4;3;5;7;9;8;6
2 05021 1 1;12
3 05028 0 2;8;6;19;30;29;28;23;20;18;7
4 05028 1 2;11;9;10

转自： http://blog.sina.com.cn/s/blog_4ce6d3de01000btv.html