oracle自定义聚合函数

第一步: 实现 ODCIAggregate 接口(定义type、type body)

CREATE TYPE SpatialUnionRoutines(

       STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,

    MEMBER FUNCTION ODCIAggregateIterate(...) ... ,

    MEMBER FUNCTION ODCIAggregateMerge(...) ...,

      MEMBER FUNCTION ODCIAggregateTerminate(...)

);

CREATE TYPE BODY SpatialUnionRoutines IS 

...

END;

第2步: 定义自定义函数

CREATE FUNCTION SpatialUnion(x Geometry) RETURN Geometry 

AGGREGATE USING SpatialUnionRoutines;

第3: 使用自定义聚合函数

SELECT SpatialUnion(geometry) FROM counties GROUP BY state

下面为具体例子:

1、定义type

create or replace type STRCAT_TYPE as object (

    CAT_STRING varchar2(4000),

    static Function ODCIAGGREGATEINITIALIZE (CS_CTX IN OUT strcat_type)

       return number,

    member Function ODCIAGGREGATEITERATE (SELF IN OUT strcat_type,VALUE IN varchar2)