SUMC函数子表转成一行显示

最新推荐文章于 2022-06-20 10:14:53 发布

David锋

最新推荐文章于 2022-06-20 10:14:53 发布

阅读量361

点赞数 1

CC 4.0 BY-SA版权

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

本文链接：https://blog.youkuaiyun.com/qq_31806719/article/details/86687369

数据库专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍如何在Oracle数据库中创建自定义聚合函数，包括类型定义、初始化、迭代、终止和合并过程。通过实例展示了字符串拼接聚合函数的实现方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE FUNCTION SUMC (input   VARCHAR2)   RETURN   VARCHAR2
  PARALLEL_ENABLE   AGGREGATE   USING   TypeSumVarchar;
  


CREATE OR REPLACE TYPE "TYPESUMVARCHAR"                                          as object
  (
   sum VARCHAR2(32767),
      static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar)  return number,
      member function ODCIAggregateIterate(self IN OUT TypeSumVarchar,value IN varchar2) return number,
      member function ODCIAggregateTerminate(self IN TypeSumVarchar,returnValue OUT VARCHAR2, flags IN number)
        return number,
      member function ODCIAggregateMerge(self IN OUT TypeSumVarchar,ctx2 IN TypeSumVarchar) return number
  )
/
CREATE OR REPLACE TYPE BODY "TYPESUMVARCHAR" IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPESUMVARCHAR)
    RETURN NUMBER IS
  BEGIN
    SCTX := TYPESUMVARCHAR('');
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT TYPESUMVARCHAR,
                                       VALUE IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    SELF.SUM := SELF.SUM || ',' || VALUE;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN TYPESUMVARCHAR,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURNVALUE := SUBSTR(SELF.SUM, 2);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPESUMVARCHAR,
                                     CTX2 IN TYPESUMVARCHAR) RETURN NUMBER IS
  BEGIN
    RETURN ODCICONST.SUCCESS;
  END;
END;
/
CREATE OR REPLACE FUNCTION "SUMC" (input   VARCHAR2)   RETURN   VARCHAR2
  PARALLEL_ENABLE   AGGREGATE   USING   TypeSumVarchar;
/
CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000)
/
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);
        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;
    RETURN;
END splitstr;
/