User-Defined Aggregate Functions Interface（自定义聚合函数，也可作为分析函数）

Oracle® Database Data Cartridge Developer's Guide,
10g Release 2 (10.2)

User-Defined Aggregate Functions

The methods in this section are implemented as methods in an object type. The CREATE FUNCTION statement is used to actually create the aggregate function. Table 22-1 summarizes these functions.

Table 22-1 Summary of User-Defined Aggregate Functions

Function	Description
ODCIAggregateInitialize()	Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
ODCIAggregateIterate()	Iterates through input rows by processesing the input values, updating and then returning the aggregation context.
ODCIAggregateMerge()	Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
ODCIAggregateTerminate()	Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
ODCIAggregateDelete()	Removes an input value from the current group.
ODCIAggregateWrapContext()	Integrates all external pieces of the current aggregation context to make the context self-contained.

ODCIAggregateInitialize()

Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter. F Implement this routine as a static method.

Syntax

STATIC FUNCTION ODCIAggregateInitialize(
   actx IN OUT ) 
RETURN NUMBER

Parameter	In/Out	Description
actx	IN OUT	The aggregation context that is initialized by the routine. This value is NULL for regular aggregation cases. In aggregation over windows, actx is the context of the previous window. This object instance is passed in as a parameter to the next aggregation routine.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

ODCIAggregateIterate()

Iterates through input rows by processesing the input values, updating and then returning the aggregation context. Invoked for each value, including NULLs. This is a mandatory routine and is implemented as a member method.

Syntax

MEMBER FUNCTION ODCIAggregateIterate(
   self IN OUT , 
   val ) RETURN NUMBER;

Parameter	IN/OUT	Description
self	IN OUT	As input, the value of the current aggregation context; as output, the updated value.
val	IN	The input value which is being aggregated.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

ODCIAggregateMerge()

Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate. This is a mandatory routine and is implemented as a member method.

Syntax

MEMBER FUNCTION ODCIAggregateMerge(
   self IN OUT , 
   ctx2 IN ) RETURN NUMBER;

Parameter	IN/OUT	Description
self	IN OUT	On input, the value of the first aggregation context; on output, the resulting value of the two merged aggregation contexts.
ctx2	IN	The value of the second aggregation context.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

ODCIAggregateTerminate()

Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory. Invoked by Oracle as the last step of aggregate computation. This is a mandatory routine and is implemented as a member method.

Syntax

MEMBER FUNCTION ODCIAggregateTerminate(
   self IN , 
   ReturnValue OUT , 
   flags IN number) RETURN NUMBER;

Parameter	IN/OUT	Description
self	IN	The value of the aggregation context.
ctx2	OUT	The resultant aggregation value.
flags	IN	A bit vector that indicates various options. A set bit of ODCI_AGGREGATE_REUSE_CTX indicates that the context will be reused and any external context should not be freed.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

See Aso:

"Reusing the Aggregation Context for Analytic Functions" on page 11-6 for details on setting the ODCI_AGGREGATE_REUSE_CTX flag bit.

ODCIAggregateDelete()

Removes an input value from the current group. The routine is invoked by Oracle by passing in the aggregation context and the value of the input to be removed during It processes the input value, updates the aggregation context, and returns the context. This is an optional routine and is implemented as a member method.

Syntax

MEMBER FUNCTION ODCIAggregateDelete(
   self IN OUT , 
   val ) RETURN NUMBER;

Parameter	IN/OUT	Description
self	IN OUT	As input, the value of the current aggregation context; as output, the updated value.
val	IN	The input value which is being removed from the current group.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

ODCIAggregateWrapContext()

Integrates all external pieces of the current aggregation context to make the context self-contained. Invoked by Oracle if the user-defined aggregate has been declared to have external context and is transmitting partial aggregates from slave processes. This is an optional routine and is implemented as a member method.

Syntax

MEMBER FUNCTION ODCIAggregateWrapContext(
   self IN OUT ) RETURN NUMBER;

Parameter	IN/OUT	Description
self	IN	On input, the value of the current aggregation context; on output, the self-contained combined aggregation context.

Returns

ODCIConst.Success on success, or ODCIConst.Error on error.

e.g.

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23590362/viewspace-1034012/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23590362/viewspace-1034012/