oracle自定义聚集函数采用oracle提供的接口,是一个固定的模式。要定义oracle聚集函数需要定义一下4个函数。
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
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
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)
终止聚集函数的处理,返回聚集函数处理的结果。
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