ANBOB™

在 Oracle 数据库中，可以使用 PL/SQL 语言来创建自定义聚合函数。自定义聚合函数的创建过程与标准 SQL 函数的创建有所不同，需要以下几个步骤：

创建一个对象类型，用于保存聚合函数的状态。
创建一个方法（成员函数）来初始化状态。
创建一个方法来累加状态。
创建一个方法来合并状态（在并行执行时需要）。
创建一个方法来返回最终结果。
创建一个聚合函数，将上述方法组合在一起。

1：创建对象类型

CREATE OR REPLACE TYPE WeightedAvgState AS OBJECT (
    total_weighted_sum NUMBER,
    total_weight NUMBER,
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT WeightedAvgState) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT WeightedAvgState, value IN NUMBER, weight IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN WeightedAvgState, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT WeightedAvgState, ctx2 IN WeightedAvgState) RETURN NUMBER
);
/

oracle 的聚集函数的语法很固定

ODCIAggregateInitialize 初始化聚合状态。
ODCIAggregateIterate 在每次迭代时更新聚合状态。
ODCIAggregateTerminate 计算并返回最终结果。
ODCIAggregateMerge 在并行执行时合并多个部分聚合结果。

ODCI是oracle 有c或都是java打包打进系统的，如果找具体是如何实现..

目前只在RDBMS/CATODCI.SQL中找到了定义的接口

--/*****************************************************************/
--/*  ODCIAggregate Interface                                      */
--/*****************************************************************/
--
-- CREATE interface ODCIAggregate AS
-- (
--   STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT <impl-type>)
--     RETURN NUMBER,
--
--   MEMBER FUNCTION ODCIAggregateIterate(self IN OUT <impl-type>,
--                                        arg IN <input-type>)
--     RETURN NUMBER,
--
--   MEMBER FUNCTION ODCIAggregateTerminate(self IN <impl-type>,
--                                          result OUT <output-type>,
--                                          flags IN NUMBER)
--     RETURN NUMBER,
--
--   MEMBER FUNCTION ODCIAggregateMerge(self IN OUT <impl-type>,
--                                      sctx2 IN <impl-type>)
--     RETURN NUMBER,
--
--   /* Optional methods */
--
--   MEMBER FUNCTION ODCIAggregateWrapContext(self IN OUT <impl-type>)
--     RETURN NUMBER,
--
--   MEMBER FUNCTION ODCIAggregateDelete(self IN OUT <impl-type>,
--                                       arg IN <input-type>)
--     RETURN NUMBER
--
-- );

就像java一样，声明了接口，具体类就要实现我的接口， 就像上面的sql 那部分应该定一个class抽象类 实现了这个接口，然后在type body部分实现具体的方法,static 常量，self 像java 里的this,呵呵，看来以前学的java有点小用。

2：定义对象类型的方法

CREATE OR REPLACE TYPE BODY WeightedAvgState IS

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT WeightedAvgState) RETURN NUMBER IS
    BEGIN
        sctx := WeightedAvgState(0, 0);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT WeightedAvgState, value IN NUMBER, weight IN NUMBER) RETURN NUMBER IS
    BEGIN
        self.total_weighted_sum := self.total_weighted_sum + value * weight;
        self.total_weight := self.total_weight + weight;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN WeightedAvgState, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        IF self.total_weight = 0 THEN
            returnValue := NULL;
        ELSE
            returnValue := self.total_weighted_sum / self.total_weight;
        END IF;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT WeightedAvgState, ctx2 IN WeightedAvgState) RETURN NUMBER IS
    BEGIN
        self.total_weighted_sum := self.total_weighted_sum + ctx2.total_weighted_sum;
        self.total_weight := self.total_weight + ctx2.total_weight;
        RETURN ODCIConst.Success;
    END;

END;
/

3：创建聚合函数
定义完成对象类型后，创建聚合函数，将对象类型的方法组合在一起。

CREATE OR REPLACE FUNCTION WeightedAvg (value NUMBER, weight NUMBER) RETURN NUMBER
AGGREGATE USING WeightedAvgState;
/

仅供参考，如发现有误请指正

打赏

微信扫一扫，打赏作者吧～