第一步: 實作 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;
以上最終效果如下：
附一：具體表結構、資料如下：
附二：具體使用方法如下：
