DM 支持自定义集函数，它不仅可以对复杂数据类型进行处理，还支持复杂的聚集计算。自定义集函数的使用和 count（）等系统提供的集函数类似，可以在 DQL(数据查询语言)和 DML 中正常使用。
21.1 创建自定义集函数
自定义集函数的创建包括两个步骤：
一 创建对象类型，实现 ODCIAggregate 接口。ODCIAggregate 接口是在对象类型中实现的。具体为使用 CREATE TYPE 语句创建相应的自定义类型。
二 创建集函数。创建自定义集函数的过程是通过指定对象类型实现的。下面对这两个步骤进行详细介绍
21.1.1 创建对象类型
DM 须创建一个对象类型以实现 ODCIAggregate 接口。创建对象类型需要使用 CREATE TYPE 语句，有关具体语法的详细介绍，请参考本手册[13.1 创建类型](#13.1 创建类型)。本章节将主要介绍对象类型中 的方法。
ODCIAggregate 接口分为必选接口和可选接口两种。必选接口是必须在自定义对象类型中实现的，可选接口用户根据需要自行选择。
21.1.1.1 必选接口
创建用户定义集函数须用到四个必选接口来实现集函数的所有内部操作：即初始化 ODCIAggrecateInitialize()，计算 ODCIAggregateIterate()、合并 ODCIAggregateMerge()、终止 ODCIAggregateTerminate()。
1. ODCIAggregateInitialize()
用于初始化实现对象类型的聚合上下文。DM 调用该程序以初始化用户定义的聚集计算，初始化的聚合上下文作为对象类型实例传递回 DM。只有完成聚合上下文的初始化才能进行后续聚合操作。
语法格式
STATIC FUNCTION ODCIAggregateInitialize(
actx IN OUT <impltype>)
RETURN NUMBER
参数
actx(输入/输出参数)：由接口初始化的聚合上下文。对于常规聚合情况，此值为 NULL。在窗口聚合中，actx 是前一个窗口的上下文。此对象实例作为参数传递给下一个聚合接口。<implitype> 为自定义对象类型。
返回值
0 成功；1 失败。
2. ODCIAggregateIterate()
通过处理输入值、计算并返回聚合上下文来迭代输入行，为每个值调用。在整个计算过程中，DM 会反复调用改程序。每次调用都会传入一个或一组新值，以及当前的聚合上下文。程序处理新值后返回更新后的聚合上下文。DM 会对本组每个非空值调用此进程，NULL 则会被忽略，不会传递给该程序。
语法格式
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT <impltype>,
val <inputdatatype>)
RETURN NUMBER
参数
self（输入/输出参数）：作为输入，是当前聚合上下文的值；作为输出，是更新后的值。<impltype> 为自定义对象类型。
val（输入参数）：待聚合的值。<inputdatatype> 为输入参数类型，该参数类型可以为 DM 支持 ROWID 除外的任何数据类型，也可以为任何不包含 ROWID 的自定义数据类型。
返回值
0 成功；1 失败。
3. ODCIAggregateMerge()
在对用户定义的聚合进行串行或并行计算期间，将两个聚合上下文合并到一个对象实例中。该接口由 DM 调用，将两个聚合上下文作为输入，返回二者组合后的新聚合上下文。该函数仅语法支持。
语法格式
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT <impltype>,
ctx2 IN <impltype>)
RETURN NUMBER
参数
self（输入/输出参数）：作为输入，是第一个聚合上下文的值；作为输出，是合并两个聚合上下文后的结果值，<impltype> 为自定义对象类型 。
ctx2（输入参数）：第二个聚合上下文的值。
返回值
0 成功；1 失败。
4. ODCIAggregateTerminate()
计算聚合的结果后输出并执行所有必要的清理，例如释放内存。该方法作为聚合计算的最后一步，由 DM 调用。该程序将聚合上下文作为输入，返回生成的聚合值。
语法格式
MEMBER FUNCTION ODCIAggregateTerminate(
self IN <impltype>,
returnvalue OUT <return_type>,
flags IN number)
RETURN NUMBER
参数
self（输入参数）：聚合上下文的值，<impltype> 为自定义对象类型。
returnvalue（输出参数）：结果聚合值。<return_type> 为输入参数类型，该参数类型可以为 DM 支持 ROWID 除外的任何数据类型，也可以为任何不包含 ROWID 的自定义数据类型。
flags（输入/输出参数）： 表示各种选项的位向量。ODCI_AGGREGATE_REUSE_CTX 的一个设置位表示上下文被重用，不应释放外部上下文。
返回值
0 成功；1 失败。
21.1.1.2 可选接口
除了四个必选接口之外，DM 还提供两个可选接口 ODCIAggregateDelete()和 ODCIAggregateWrapContext()。
1. ODCIAggregateDelete()
该计算用于从当前聚合上下文中删除某一行已聚合的值，恢复没有该行参与的聚合上下文。使用该成员方法可以实现重用聚合上下文。该成员方法仅语法支持。
语法格式
MEMBER FUNCTION ODCIAggregateDelete(
self IN OUT <impltype>,
val <inputdatatype>)
RETURN NUMBER
参数
self（输入/输出参数）：作为输入，是目前聚合上下文的值；作为输出，是删除某一聚合值更新后的聚合上下文 。<impltype> 为自定义对象类型
val（输入参数）：待删除值。<inputdatatype> 为输入参数类型，该参数类型可以为 DM 支持 ROWID 除外的任何数据类型，也可以为任何不包含 ROWID 的自定义数据类型。
返回值
0 成功；1 失败。
2. ODCIAggregateWrapContext()
集成当前聚合上下文的所有外部片段，使上下文自包含。如果用户定义的聚合被声明为具有外部上下文，并且正在从从属进程传输部分聚合，则由 DM 调用该方法。该成员方法仅语法支持。
语法格式
MEMBER FUNCTION ODCIAggregateWrapContext(
self IN OUT <impltype>)
RETURN NUMBER
参数
self（输入/输出参数）：作为输入：当前聚合上下文的值；作为输出：自包含的聚合上下文的值。<impltype> 为自定义对象类型。
返回值
0 成功；1 失败。
21.1.2 创建集函数
语法格式
CREATE [OR REPLACE] FUNCTION [<模式名>.]<函数名>[(<参数列表>)]
RETURN <返回值类型>
[<usedf_agg_keywords>]AGGREGATE USING [<模式名>.]<类对象名>
<userdf_agg_keywords>::=PARALLEL_ENABLE[DETERMINISTIC]|
DETERMINISTIC[PARALLEL_ENABLE]
参数：
- < 函数名 > 指被创建的自定义集函数的名称；
- < 模式名 > 指明被创建的自定义集函数所属模式的名称，缺省为当前模式名；
- < 参数列表 > 指明自定义集函数信息，参数模式可设置为 IN、OUT 或 IN OUT（OUT IN），缺省为 IN 类型，参数类型和参数个数需和自定义对象类型中定义保持一致；
- < 返回值类型 > 必须和自定义对象类型中定义的一致；
- DETERMINISTIC：当使用用户自定义函数进行物化视图创建时，为保证物化视图能够进行查询改写（QUERY REWRITE），需对用户自定义函数添加该关键字；
- PARALLEL_ENABLE：指定该用户自定义函数可以进行并行聚集，仅语法支持；
- < 自定义对象类型名 > 实现 ODCIAggergate 接口的对象类型名称。
图例
自定义集函数创建：
语句功能
创建自定义集函数
使用说明
创建自定义集函数在语法上和创建存储函数类似，但需要注意的是，创建自定义集函数不支持“IF NOT EXISTS”。
21.2 使用场景
自定义集函数的使用和系统集函数的使用几乎完全相同。其主要用于以下几种情景中：
一 用于 DQL 语句中，可以用于 SELECT 子句；也可用于 ORDER BY 子句或者作为 HAVING 子句中的部分谓词出现；还可以与 DISTINCT(忽略重复值)、ALL(缺省值)、 ROLLUP 或 CUBE 等关键字联合使用。需注意的是，在与 ROLLUP，CUBE 联用时，情况会和系统集函数略有不同，系统在产生结果的基础上进行了排序。
二 用于 DML 语句中，一般借助查询语句使用。
三 用于 DDL 语句中，可以用于表的创建和视图的创建，一般借助查询语句使用。需要注意的是，对于用于物化视图定义的自定义集函数，需要用到关键字 DETERMIINISTIC，以保证物化视图能够进行查询改写。
四 用于分析函数中。自定义集函数可以和 OVER(< 分析子句 >)组合之后作为分析函数使用，使用方式和系统提供的分析函数完全一致，OVER(< 分析子句 >)具体使用方式可参考本手册 4.1.4 分析函数。
21.3 删除自定义集函数
删除自定义集函数使用 DROP FUNCTION 完成。删除之前，建议用户先删除相关的自定义对象类型。
语法格式
DROP FUNCTION [IF EXISTS] [<模式名>.]<自定义集函数名>;
图例
删除自定义集函数
使用说明
- 删除不存在的函数会报错。若指定 IF EXISTS 关键字，删除不存在的集函数不会报错；
- 如果被删除的集函数不属于当前模式，必须在语句中指明模式名；
权限
执行该操作的用户必须是 DBA，或者是函数的拥有者且具有 DROP PACKAGE 权限。
21.4 应用实例
编写一个自定义集函数 MyMax，用于查找最大值，功能和系统集函数完全一致。下面将针对其创建、使用和删除过程，进行具体描述。
21.4.1 创建
首先，删除可能存在的同名对象类型和同名函数。
DROP TYPE IF EXISTS MyMaxImpl;
DROP FUNCTION IF EXISTS MyMax;
其次，创建对象类型 MyMaxImpl，在对象类型中实现声明 ODCIAggregate 接口。
CREATE TYPE MyMaxImpl AS OBJECT
(
max NUMBER, -- 目前最大值
//初始化
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT MyMaxImpl)
RETURN NUMBER,
//迭代计算
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT MyMaxImpl,
value IN NUMBER) RETURN NUMBER,
//合并
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT MyMaxImpl,
ctx2 IN MyMaxImpl) return number,
//终止
MEMBER FUNCTION ODCIAggregateTerminate(self IN MyMaxImpl,
returnValue OUT NUMBER, flags IN NUMBER) RETURN number
);
/
接着，创建类型体 MyMaxImpl，对 ODCIAggregate 接口进行实现。
对于初始化，直接设置初始值为 0；对于迭代部分，比较聚合的新值和目前聚合值的最大值进行比较，取较大值赋给 max；对于合并部分，将两个并行聚合产生的两个聚合上下文中的 max 进行对比，并取最大值作为结果输出；对于终止部分，聚合上下文中的 max 即为结果值，所以直接输出即可。
CREATE OR REPLACE TYPE BODY MyMaxImpl IS
//初始化
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT MyMaxImpl)
RETURN NUMBER IS
BEGIN
sctx := MyMaxImpl(0);
return ODCIConst.Success;
END;
//迭代
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT MyMaxImpl, value IN NUMBER) RETURN NUMBER IS
BEGIN
IF value > self.max THEN
self.max := value;
END IF;
RETURN ODCIConst.Success;
END;
//合并
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT MyMaxImpl, ctx2 IN MyMaxImpl) RETURN NUMBER IS
BEGIN
IF ctx2.max > self.max THEN
self.max := ctx2.max;
END IF;
RETURN ODCIConst.Success;
END;
//终止
MEMBER FUNCTION ODCIAggregateTerminate(self IN MyMaxImpl,
returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnValue := self.max;
RETURN ODCIConst.Success;
END;
END;
/
然后，创建自定义集函数 MyMax。
CREATE FUNCTION MyMax (INPUT NUMBER) RETURN NUMBER
AGGREGATE USING MyMaxImpl;
21.4.2 使用
- 用于 DQL 语句中
例 1 自定义集函数 MyMax 用于 SELECT 子句查找最大薪资项。
SELECT MyMax(SALARY)
FROM RESOURCES.EMPLOYEE;
结果如下
行号 MYMAX(SALARY)
---------- -------------
1 40000
例 2 自定义集函数 MyMax 用于 ORDER BY 和 HAVING 子句查找每个职位的最高薪资。
SELECT TITLE, MyMax(SALARY)
FROM RESOURCES.EMPLOYEE
GROUP BY TITLE
HAVING MyMax(SALARY)>10000
ORDER BY MyMax(SALARY);
结果如下：
行号 TITLE MYMAX(SALARY)
---------- -------------- -------------
1 采购代表 12000
2 销售代表 16000
3 系统管理员 20000
4 采购经理 23000
5 人力资源部经理 25000
6 销售经理 26000
7 总经理 40000
例 3 自定义集函数 MyMax 与关键字 ROLLUP 联用查询每个出版社同一批出版的图书的最大 ID 号。
SELECT MYMAX(PRODUCTID),PUBLISHER, PUBLISHTIME
FROM PRODUCTION.PRODUCT
GROUP BY ROLLUP(PUBLISHER,PUBLISHTIME);
结果如下：
行号 MYMAX(PRODUCTID) PUBLISHER PUBLISHTIME
---------- ---------------- -------------------- -----------
1 5 2006-09-01
2 10 21世纪出版社 1901-01-01
3 4 广州出版社 2005-12-01
4 8 机械工业出版社 2006-01-01
5 7 清华大学出版社 2007-03-01
6 6 人民文学出版社 2006-09-01
7 3 上海出版社 2006-08-01
8 9 外语教学与研究出版社 2003-08-01
9 2 中华书局 2005-04-01
10 5 NULL
11 10 21世纪出版社 NULL
12 4 广州出版社 NULL
13 8 机械工业出版社 NULL
14 7 清华大学出版社 NULL
15 6 人民文学出版社 NULL
16 3 上海出版社 NULL
17 9 外语教学与研究出版社 NULL
18 2 中华书局 NULL
19 10 NULL NULL
- 用于 DML 语句中
例 薪资最高者加薪 5000 元。
为进行对比，首先查看最高薪资
SELECT MyMax(SALARY) FROM RESOURCES.EMPLOYEE;
结果如下所示：
行号 MYMAX(SALARY)
---------- -------------
1 40000
使用 DML 语句对薪资最高者数据进行更新
UPDATE RESOURCES.EMPLOYEE SET SALARY=SALARY+5000 WHERE SALARY IN (SELECT MYMAX(SALARY) FROM RESOURCES.EMPLOYEE);
更新后再次查看
SELECT MyMax(SALARY) FROM RESOURCES.EMPLOYEE;
结果如下所示：
行号 MYMAX(SALARY)
---------- -------------
1 45000
- 用于 DDL 语句中
例 1 使用自定义集函数创建表用于查看每个岗位的最高薪资
DROP TABLE IF EXISTS T1;
CREATE TABLE T1 AS SELECT TITLE, MyMax(SALARY)AS MAXSAL
FROM RESOURCES.EMPLOYEE
GROUP BY TITLE;
SELECT * FROM T1;
结果如下所示：
行号 TITLE MAXSAL
---------- -------------- ------
1 采购代表 12000
2 采购经理 23000
3 人力资源部经理 25000
4 系统管理员 20000
5 销售代表 16000
6 销售经理 26000
7 总经理 40000
例 2 使用自定义集函数创建允许查询改写的物化视图。
//创建带关键字DETERMINISTIC的自定义集函数
DROP FUNCTION IF EXISTS MyMax;
CREATE FUNCTION MyMax (INPUT NUMBER) RETURN NUMBER
DETERMINISTIC
AGGREGATE USING MyMaxImpl;
//创建允许查询改写的物化视图
DROP MATERIALIZED VIEW IF EXISTS MYVIEW;
CREATE MATERIALIZED VIEW MYVIEW
ENABLE QUERY REWRITE AS
SELECT MyMax(SALARY)
FROM RESOURCES.EMPLOYEE;
//查询该物化视图
SELECT * FROM MYVIEW;
结果如下所示：
行号 MYMAX(SALARY)
---------- -------------
1 40000
- 用于分析函数
例 查询折扣大于 7 的图书作者以及最大折扣。此时的 MyMax 作为一个分析函数。
SELECT AUTHOR, MyMax(DISCOUNT) OVER (PARTITION BY AUTHOR) AS MYMAX
FROM PRODUCTION.PRODUCT
WHERE DISCOUNT > 7;
结果如下所示：
行号 AUTHOR MYMAX
---------- -------------- -----
1 曹雪芹,高鹗 8
2 施耐庵，罗贯中 7.5
3 严蔚敏，吴伟民 8.5
21.4.3 删除
删除自定义集函数 MyMax 之前，建议先删除自定义对象类型 MyMaxImpl。
DROP TYPE IF EXISTS MyMaxImpl;
DROP FUNCTION IF EXISTS MyMax;
