SUMC 函数

SUMC使用场景：如查询同名身份证号有哪些，假设两张表：PUB_USER（用户表）PUB_CARD（身份信息表），这样同名张三的有两人结果就是一条记录：(张三ID1，张三ID2) 张三
SELECT SUMC(C.CARD_ID),U.USER_NAME
FROM PUB_USER U,PUB_CARD C
WHERE U.USER_ID = C.USER_ID
GROUP BY U.USER_NAME

--创建一个类型TypeSumVarchar
create or replace type TypeSumVarchar as object
(
sum VARCHAR2(4000),
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:=SUBSTRB(self.sum||','||value,0,4000); --此处是函数的实现部分;当长度超过4000将被截取
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;
/