1、创建自定义聚合函数接口
CREATE OR REPLACE TYPE TYPE_AGG AS OBJECT
(
BASEVALUE NUMBER,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPE_AGG)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPE_AGG,
VALUE IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPE_AGG,
CTX2 IN TYPE_AGG)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN TYPE_AGG,
RETURNVALUE OUT NUMBER,
FLAGS IN NUMBER)
RETURN NUMBER
)
2、实现自定义聚合函数
CREATE OR REPLACE TYPE BODY TYPE_AGG IS
--实现过程
END;
3、函数绑定
CREATE OR REPLACE FUNCTION Fun_Name(paras VARCHAR2) RETURN NUMBER
AGGREGATE USING TYPE_AGG;
或
CREATE OR REPLACE FUNCTION Fun_Name(paras VARCHAR2) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING TYPE_AGG;--有并行处理
自定义sum函数
创建T_SUM：
CREATE OR REPLACE TYPE T_SUM AS OBJECT
(
BASEVALUE NUMBER,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_SUM) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_SUM, VALUE IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_SUM,
RETURNVALUE OUT NUMBER,
FLAGS IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_SUM, CTX2 IN T_SUM) RETURN NUMBER
)
创建T_SUM包体：
create or replace type body T_SUM is
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_SUM) RETURN NUMBER IS
BEGIN
SCTX := T_SUM(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_SUM, VALUE IN NUMBER) RETURN NUMBER IS
BEGIN
SELF.BASEVALUE := nvl(SELF.BASEVALUE,0) + VALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_SUM, CTX2 IN T_SUM) RETURN NUMBER IS
BEGIN
SELF.BASEVALUE := SELF.BASEVALUE + CTX2.BASEVALUE;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_SUM,
RETURNVALUE OUT NUMBER,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SELF.BASEVALUE;
RETURN ODCICONST.SUCCESS;
END;
end;
绑定TYPE：
CREATE OR REPLACE FUNCTION SUM_AGG(p_num NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING T_SUM;
