About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc
全部博文（148）
分类： Oracle
2020-06-30 17:01:45
参考《Database
Data Cartridge Developer's Guide》第22章：2User-Defined
Aggregate Functions Interface
Oracle有预定义的组函数或分析函数，比如MAX,MIN。但是这些函数可能无法处理复杂的类型，比如CLOB，或者无法处理复杂的数据处理。Oracle允许自定义组函数，用于实现组函数或分析函数的功能。
用户自定义组函数或分析函数，需要实现ODCIAggregate接口里的至少4个函数，分别是initialization,
iteration, merging, and termination。如下图所示：
然后按照步骤和规范实现上述接口，则可以使用自定义的组函数和分析函数。下面就以自定义的WM_CONCAT为例，如下所示：
1.定义对象，包括处理的数据变量以及对应的4个函数
CREATE OR REPLACE
TYPE WM_CONCAT_10G_IMPL AUTHID CURRENT_USER AS OBJECT
(
CURR_STR
VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX
IN OUT WM_CONCAT_10G_IMPL)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN
OUT WM_CONCAT_10G_IMPL,
P1 IN
VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAGGREGATETERMINATE(SELF IN
WM_CONCAT_10G_IMPL,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_10G_IMPL,
SCTX2
IN WM_CONCAT_10G_IMPL)
RETURN NUMBER
);
/
2.实现对象的4个方法
|
CREATE OR REPLACE TYPE BODY WM_CONCAT_10G_IMPL IS ---初始化方法，初始化为NULL STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_10G_IMPL) RETURN NUMBER IS BEGIN SCTX := WM_CONCAT_10G_IMPL(NULL); RETURN ODCICONST.SUCCESS; END;
--核心逻辑，迭代，也就是按照逗号连接 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_10G_IMPL, 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 WM_CONCAT_10G_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR; RETURN ODCICONST.SUCCESS; END;
--合并处理 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_10G_IMPL, SCTX2 IN WM_CONCAT_10G_IMPL) 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; /
|
3.定义函数
|
create or replace FUNCTION wm_concat_10g(P1 VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING wm_concat_10g_impl; /
create public synonym wm_concat for wm_concat_10g;
|
如果在别的用户下创建，可以创建个同义词供其他用户使用，下面使用自定义的wm_concat做个例子。
|
--用于普通组函数 from emp group by deptno order by deptno;
DEPTNO ENAMES 10 CLARK 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING,MILLER 20 ADAMS 20 ADAMS,FORD 20 ADAMS,FORD,JONES 20 ADAMS,FORD,JONES,SCOTT 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN 30 ALLEN,BLAKE 30 ALLEN,BLAKE,JAMES 30 ALLEN,BLAKE,JAMES,MARTIN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
select deptno,wm_concat(ename) over(partition by deptno order by ename) from emp
DEPTNO ENAMES 10 CLARK 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING 10 CLARK,KING,KING,KING,MILLER 20 ADAMS 20 ADAMS,FORD 20 ADAMS,FORD,JONES 20 ADAMS,FORD,JONES,SCOTT 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN 30 ALLEN,BLAKE 30 ALLEN,BLAKE,JAMES 30 ALLEN,BLAKE,JAMES,MARTIN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
|
自定义的Aggregate Function不仅可以用于普通分组函数，也可以用于分析函数，可以指定需要的类型，用于补充内置函数的不足。
