SUMC 函数

--创建一个类型TypeSumVarchar

create or replace type TypeSumVarchar as object
(
  sum VARCHAR2(100),
  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;
/

--创建自定义聚集函数SUMC

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