Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1369358
  • 博文数量: 172
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3831
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(172)

文章存档

2024年(27)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-06-30 17:01:45

接PART7:http://blog.chinaunix.net/uid-7655508-id-5834951.html

1.4 自定义分析函数

  参考《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做个例子。


--用于普通组函数
select deptno,wm_concat(ename) enames

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不仅可以用于普通分组函数,也可以用于分析函数,可以指定需要的类型,用于补充内置函数的不足。

 


阅读(2162) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~