AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 开发指南（Oracle语法兼容2.0） SQL语法 DDL（数据定义语句） CREATE FUNCTION ... AGGREGATE USING type_name

CREATE FUNCTION ... AGGREGATE USING type_name

更新时间：
产品详情
我的收藏

CREATE FUNCTION ... AGGREGATE USING type_name语法是用户创建自定义的聚合函数。

简介

PolarDB数据库提供了几种预定义的聚合函数。例如，MAX、MIN和SUM用于对一组行执行操作。这些预定义的聚合函数只能用于标量数据，不能用于复杂的数据类型。例如使用对象类型、不透明类型和LOB存储的多媒体数据。

CREATE FUNCTION ... AGGREGATE USING type_name可以用来为复合数据类型创建自定义的聚合函数。还可以定义全新的聚合函数来处理复杂数据。用户定义的聚合函数可以在SQL DML语句中使用，如同内置聚合函数一样。

用户定义的聚合函数是可扩展性框架的一项功能，用户可以使用ODCIAggregate接口函数来实现。

用户可以通过实现一组统称为ODCIAggregate的函数来创建一个用户定义的聚合函数。可以将这些函数实现为对象类型中的方法。当定义对象类型并在类型主体中实现函数时，使用该CREATE FUNCTION语句创建聚合函数。

每个用户定义的聚合函数最多使用四个ODCIAggregate函数或步骤来定义任何聚合函数执行的内部操作，包括初始化、迭代、合并和终止。

函数名称

说明

ODCIAggregateInitialize

Oracle调用该函数来初始化用户定义聚合的计算。初始化的聚合上下文作为对象类型实例传递回Oracle。

ODCIAggregateIterate

Oracle重复调用该函数。每次调用时，都会将一个新值（或一组新值）作为输入传递。当前聚合上下文也被传入。函数处理新值并将更新的聚合上下文返回给Oracle。为基础组中的每个非NULL值调用此函数。（NULL值在聚合期间被忽略，不会传递给函数。）

ODCIAggregateMerge

Oracle调用该函数来组合两个聚合上下文。该函数将两个上下文作为输入，组合它们，并返回单个聚合上下文。

ODCIAggregateTerminate

该函数由Oracle作为聚合的最后一步调用。该函数将聚合上下文作为输入并返回生成的聚合值。

语法

CREATE TYPE SpatialUnionRoutines(
   STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
   MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
   MEMBER FUNCTION ODCIAggregateMerge(...) ...,
   MEMBER FUNCTION ODCIAggregateTerminate(...)
);

CREATE TYPE BODY SpatialUnionRoutines IS 
...
END;

CREATE FUNCTION SpatialUnion(x int) RETURN int 
AGGREGATE USING SpatialUnionRoutines;

参数说明

ODCIAggregateInitialize

STATIC FUNCTION ODCIAggregateInitialize(
   actx IN OUT <impltype>) 
RETURN NUMBER

参数

In/Out

说明

actx

IN OUT

由例程初始化的聚合上下文。该值适用NULL与常规聚合情况。在窗口聚合中，actx是前一个窗口的上下文。该对象实例作为参数传递给下一个聚合例程。

ODCIAggregateIterate

MEMBER FUNCTION ODCIAggregateIterate(
   self IN OUT <impltype>, 
   val <inputdatatype>) 
RETURN NUMBER

参数

In/Out

描述

self

IN OUT

  • 作为输入，当前聚合上下文的值。

  • 作为输出，更新值。

val

IN

正在聚合的输入值。

ODCIAggregateMerge

MEMBER FUNCTION ODCIAggregateMerge(
   self IN OUT <impltype>, 
   ctx2 IN <impltype>)
RETURN NUMBER

参数

In/Out

说明

self

IN OUT

  • 输入时，第一个聚合上下文的值。

  • 输出时，两个合并聚合上下文的结果值。

ctx2

IN

第二个聚合上下文的值。

ODCIAggregateTerminate

MEMBER FUNCTION ODCIAggregateTerminate(
   self IN <impltype>, 
   ReturnValue OUT <return_type>, 
   flags IN number) 
RETURN NUMBER

参数

In/Out

说明

self

IN

聚合上下文的值。

ReturnValue

OUT

所得聚合值。

flags

IN

指示各种选项的位向量。一组位ODCI_AGGREGATE_REUSE_CTX指示上下文被重用，并且不应释放任何外部上下文。

原理介绍（并行聚合）

用户定义聚合的并行评估

与内置聚合函数一样，用户定义的聚合可以并行计算。但是，聚合函数必须声明为启用并行。

  1. 创建函数MyUDAG(...)返回... PARALLEL_ENABLE聚合使用MyAggrRoutines。

  2. 通过聚合并行从属中的行子集生成的聚合上下文被发送回下一个并行步骤（查询协调器或下一个从集）。

  3. 调用Merge例程来合并聚合上下文，最后调用Terminate例程获取聚合值。

调用流程如下：

image.png

处理大型聚合上下文

当使用外部语言（如C或Java）实现类型方法时，每次调用实现类型方法时，都必须在数据库服务器进程和外部函数的语言环境之间来回传递聚合上下文。

传递大型聚合上下文会对性能产生不利影响。为避免这种情况，您可以将聚合上下文存储在外部内存中，在外部函数的执行环境中分配，并仅将引用或键传递给上下文而不是上下文本身。密钥应存储在实现类型实例（self）中，然后可以在数据库服务器和外部函数之间传递密钥。

将键传递给上下文而不是上下文本身可以使实现类型实例保持较小，以便可以快速传输。此策略的另一个优点是用于保存聚合上下文的内存是在函数的执行环境（例如，extproc）中分配的，而不是在数据库服务器中。

通常应该分配内存来保存聚合上下文ODCIAggregateInitialize并将对它的引用存储在实现类型实例中。在随后的调用中，可以使用引用访问外部存储器和它包含的聚合上下文。外部存储器通常应在ODCIAggregateTerminate中释放，在合并完成后，ODCIAggregateMerge释放用于存储合并上下文的外部内存（ODCIAggregateMerge的第二个参数）。

示例

  1. 声明类中函数。

    CREATE OR replace TYPE agg_test_schema_type_1 AUTHID CURRENT_USER
AS OBJECT (
    curr_str VARCHAR2 (32767),

    STATIC FUNCTION odciaggregateinitialize 
    (sctx IN OUT agg_test_schema_type_1) 
    RETURN NUMBER,

    MEMBER FUNCTION odciaggregateiterate 
    (SELF IN OUT agg_test_schema_type_1,p1 IN VARCHAR2) 
    RETURN NUMBER,

    MEMBER FUNCTION odciaggregateterminate 
    (SELF IN agg_test_schema_type_1, returnvalue OUT VARCHAR2, flags IN NUMBER)
    RETURN NUMBER,

    MEMBER FUNCTION odciaggregatemerge 
    (SELF IN OUT agg_test_schema_type_1, sctx2 IN agg_test_schema_type_1)
    RETURN NUMBER
);

  2. 定义类中函数。

    CREATE OR replace TYPE BODY agg_test_schema_type_1
IS
    STATIC FUNCTION odciaggregateinitialize (sctx IN OUT agg_test_schema_type_1)
        RETURN NUMBER
    IS
    BEGIN
        sctx := agg_test_schema_type_1(NULL);
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateiterate (
        SELF   IN OUT   agg_test_schema_type_1,
        p1     IN       VARCHAR2
    )
    RETURN NUMBER
    IS
    BEGIN
        IF (curr_str IS NOT NULL)
        THEN
            curr_str := curr_str || ',' || p1;
        ELSE
            curr_str := p1;
        END IF;
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregateterminate (
        SELF          IN       agg_test_schema_type_1,
        returnvalue   OUT      VARCHAR2,
        flags         IN       NUMBER
    )
    RETURN NUMBER
    IS
    BEGIN
        returnvalue := curr_str;
        RETURN odciconst.success;
    END;

    MEMBER FUNCTION odciaggregatemerge (
        SELF    IN OUT   agg_test_schema_type_1,
        sctx2   IN       agg_test_schema_type_1
    )
    RETURN NUMBER
    IS
    BEGIN
        IF (sctx2.curr_str IS NOT NULL)
        THEN
            SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str;
        END IF;
        RETURN odciconst.success;
    END;
END;

  3. 使用上面创建的类来创建自定义聚合函数并使用。

    CREATE OR REPLACE FUNCTION agg_test_schema_func_1(p1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING agg_test_schema_type_1;

create table agg_test_schema_table_1 (id int, c varchar2(100));

insert into agg_test_schema_table_1 values('1','a');
insert into agg_test_schema_table_1 values('1','b');
insert into agg_test_schema_table_1 values('2','b');

select id, agg_test_schema_func_1(c) from agg_test_schema_table_1 group by id;
 id | agg_test_schema_func_1 
----+------------------------
  2 | b
  1 | a,b

上一篇：CREATE FUNCTION下一篇：CREATE EXTENSION
文档推荐