oracle 自定义聚合函数（按字段分组，非分组字段进行合并）

最新推荐文章于 2023-11-23 16:31:39 发布

lqp10

最新推荐文章于 2023-11-23 16:31:39 发布

阅读量1.3k

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.csdn.net/lqp10/article/details/84762187

版权

oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了如何在Oracle数据库中创建一个自定义聚合函数，该函数可以按字段分组，并将非分组字段的值用逗号分隔的方式合并。通过示例展示了zh_concat函数的实现，包括ODCIAGGREGATEINITIALIZE, ODCIAGGREGATEITERATE, ODCIAGGREGATETERMINATE和ODCIAGGREGATEMERGE等关键方法的定义。" 116724734,10965440,Java并发编程与高并发实战解析,"['Java', '并发模型', '高并发', '分布式', '编程实战']

摘要由CSDN通过智能技术生成

按字段进行分组，没分组字段通过函数进行字符串的合并，用','分割

CREATE OR REPLACE FUNCTION zh_concat(P1 VARCHAR2) RETURN clob AGGREGATE USING zh_concat_im;
-----------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE BODY zh_concat_im IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
    RETURN NUMBER IS
BEGIN
    SCTX := zh_concat_im(NULL);
    RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
                                       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 zh_concat_im,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURNVALUE := CURR_STR;
    RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                                     SCTX2 IN zh_concat_im) 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;

--------------------------------------------------------------------------------------

CREATE OR REPLACE TYPE "ZH_CONCAT_IM"
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
    RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
                                       P1   IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN zh_concat_im,
                                         RETURNVALUE OUT clob,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
                                     SCTX2 IN zh_concat_im) RETURN NUMBER
)