应用场景:
权限管理:同一功能对同一用户有多个不同权限设置(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 ;