[转]讲解oracle自定义聚合函数

韩蓝尹

　应用场景：
　　权限管理：同一功能对同一用户有多个不同权限设置(1-允许/0-默认/-1禁用)，取组合后的权限值。
　　--聚合函数就是一个对象
　　create or replace type permission_sign_object as object (
　　--对象变量
　　permission_sign number(1),
　　--对象初始化
　　static function ODCIAggregateInitialize(v_self in out permission_sign_object) return number,
　　--聚合函数的迭代方法
　　member function ODCIAggregateIterate(self in out permission_sign_object, value in number) return number,
　　--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
　　member function ODCIAggregateMerge(self in out permission_sign_object, v_next in permission_sign_object) return number,
　　--终止聚集函数的处理,返回聚集函数处理的结果
       Oracle 自带的wmsys.wm_concat 函数是有长度限制的。然而需求是在不断变化，当自带的东西不能适应我们需求的时候，就需要发挥我们自己的智慧去扩展。
 
 

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 VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,  
SCTX2 IN zh_concat_im) RETURN NUMBER
);
/
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 VARCHAR2,
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 FUNCTION zh_concat(P1 VARCHAR2)  
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;