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)

       return number,

    member Function ODCIAGGREGATEMERGE (SELF IN Out strcat_type,CTX2 IN Out strcat_type)

       return number,

    member Function ODCIAGGREGATETERMINATE (SELF IN Out strcat_type,RETURNVALUE OUT varchar2,FLAGS IN number)

       return number

)

2、定义type body

create or replace type body STRCAT_TYPE as

   static Function ODCIAGGREGATEINITIALIZE (CS_CTX IN OUT strcat_type)

      return number as

      begin

            cs_ctx := strcat_type( null );

            return ODCIConst.Success;

        end;

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

      return number as

      begin

            --1. concat string

            self.cat_string := self.cat_string || ','|| value;

            -- 2.get union set

            -- if  instr(self.cat_string, value ) = 0 or self.cat_string is null then

            --        self.cat_string := self.cat_string || ',' || value ;

            -- else

            --        self.cat_string := self.cat_string ||'' ;

            -- end if ;

            return ODCIConst.Success;

        end;

   member Function ODCIAGGREGATEMERGE (SELF IN Out strcat_type,CTX2 IN Out strcat_type)

      return number as

      begin

            self.cat_string := self.cat_string || ',' || ctx2.cat_string;

            return ODCIConst.Success;

        end;

   member Function ODCIAGGREGATETERMINATE (SELF IN Out strcat_type,RETURNVALUE OUT varchar2,FLAGS IN number)

      return number as

      begin

            returnValue := ltrim(rtrim(self.cat_string,','),',');

            return ODCIConst.Success;

        end;

end;

3、定义函数

CREATE OR REPLACE FUNCTION strcat(input varchar2) RETURN varchar2

  PARALLEL_ENABLE

  AGGREGATE USING strcat_type;

以上最终效果如下：

 附一：具体表结构、数据如下：

 

 

附二：具体使用方法如下：