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. summarizes these functions.
Table 22-1 Summary of User-Defined Aggregate Functions
Function | Description |
---|---|
Initializes the aggregation context and instance of the implementation object
type, and returns it as an | |
Iterates through input rows by processesing the input values, updating and then returning the aggregation context. | |
Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate. | |
Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory. | |
Removes an input value from the current group. | |
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
NULL
s. 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 theODCI_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;
-------- ------------------------------------------------------------
SCOTT 32502,32501,32495,32494,32493,32492,32491,32490,32489,32488
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23590362/viewspace-1034012/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 自定義聚合函式Oracle函式
- Oracle聚合函式/分析函式Oracle函式
- mysql 自定義分析函式 least 及 日期函式MySql函式AST
- Hive常用函式及自定義函式Hive函式
- Hive FUNCTIONS函式HiveFunction函式
- 指南:函式(Functions)函式Function
- shell自定義函式函式
- Oracle 自定義函式Oracle函式
- perl自定義函式函式
- Hive函式(內建函式+自定義標準函式UDF)Hive函式
- PHP 自定義函式用法及常用函式集合PHP函式
- java自定義equals函式和hashCode函式Java函式
- Swift-函式(Functions)Swift函式Function
- spark aggregate函式Spark函式
- hive 3.0.0自定義函式Hive函式
- Hive中自定義函式Hive函式
- MySQL建立自定義函式MySql函式
- python 自定義函式Python函式
- mysql自定義函式篇MySql函式
- Oracle中自定義函式Oracle函式
- 一個自定義函式函式
- Oracle自定義聚集函式Oracle函式
- ORACLE 自定義函式BUG?Oracle函式
- SQL SERVER 自定義函式SQLServer函式
- SQL 自定義函式FUNCTIONSQL函式Function
- 聚合函式與數字函式函式
- JavaScript 設計模式系列 – 自定義函式(惰性函式)JavaScript設計模式函式
- 動畫函式的繪製及自定義動畫函式動畫函式
- JavaScript 設計模式系列 - 自定義函式(惰性函式)JavaScript設計模式函式
- MySQL函式大全(字串函式,數學函式,日期函式,系統級函式,聚合函式)MySql函式字串
- Stream聚合函式函式
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- Day6 函式(Functions)函式Function
- 函式索引使用細節——自定義函式的索引化函式索引
- laravel 自定義全域性函式Laravel函式
- Laravel 自定義函式存放位置Laravel函式
- Laravel 新增自定義助手函式Laravel函式
- Laravel自定義輔助函式Laravel函式