Oracle深入之自定义聚合函数（字符串数组去重，统计子串个数）

一、概述

Oracle提供了很多预定义好的聚集函数，比如Max(), Sum(), AVG()， 但是这些预定义的聚集函数基本上都是适应于标量数据(scalar data)， 对于复杂的数据类型，比如说用户自定义的Object type, Clob等， 是不支持的。

但是，用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface来创建自定义聚集函数，而且自定义的聚集函数跟内建的聚集函数用法上没有差别。

通过实现 ODCIAggregate rountines来创建自定义的聚集函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)， 可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后， 就可以通过CREATE FUNCTION语句来创建自定义的聚集函数了。

每个自定义的聚集函数需要实现4个ODCIAggregate 接口函数， 分别是：、迭代、合并和终止。这些函数定义了任何一个聚集函数内部需要实现的操作，这些函数分别是 初始化（initialization）, 迭代（iteration）, 合并（merging） 和 终止（termination）。

函数	描述
ODCIAggregateInitialize	Oracle 调用此例程来初始化用户定义聚合的计算。初始化的聚合上下文作为对象类型实例传回 Oracle。
ODCIAggregateIterate	Oracle 反复调用此例程。每次调用时，都会传递一个新值（或一组新值）作为输入。当前的聚合上下文也被传入。例程处理新值并将更新的聚合上下文返回给 Oracle。NULL为基础组中的每个非值调用此例程。（NULL值在聚合期间被忽略，并且不传递给例程。）
ODCIAggregateMerge	Oracle 调用此例程来组合两个聚合上下文。该例程将两个上下文作为输入，将它们组合起来，然后返回一个聚合上下文。
ODCIAggregateTerminate	Oracle 调用此例程作为聚合的最后一步。该例程将聚合上下文作为输入并返回结果聚合值。

---

二、使用

本例子是利用数组去重，统计一个字段中不同子串的个数

1.创建数组（Type）

--创建数组对象
CREATE OR REPLACE TYPE "TYPE_STR" is table of varchar2(255);

2.创建自定义对象（OBJECT Type）

--创建自定义对象
CREATE OR REPLACE TYPE Count_Substr_Type AS OBJECT
(
  --用户自定义的参数，按顺序从上到下，初始化则是从左到右:Count_Substr_Type(to_clob(','),',',TYPE_STR())
  
  --字符串值
  concat_str CLOB,
  
  --分割符号
  splitstr varchar2(64),
  
  --自定义变量，字符串数组，初始化为空数组
  str_array TYPE_STR,
  
--ODCIAggregateInitialize做一些初始化操作

  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out Count_Substr_Type)
    return number,

--ODCIAggregateIterate是主要的处理逻辑所在，这里定义一个迭代操作

  member function ODCIAggregateIterate(self     In Out Count_Substr_Type,
                                       curvalue in VARCHAR2) return number,

-- ODCIAggregateMerge是一个合并函数，如果在使用时指定了partition enabled，就必须定义此函数，用来把并行处理的结果进行合并

  member function ODCIAggregateMerge(self In Out Count_Substr_Type,
                                     ctx2 In Out Count_Substr_Type)
    return number,

--ODCIAggregateTerminate是一个终止函数，顾名思义，在这个函数中对结果做最后处理并返回

  member function ODCIAggregateTerminate(self        In Out Count_Substr_Type,
                                         returnValue Out number,
                                         flags       IN NUMBER)
    return number

)
/

--创建自定义对象体
CREATE OR REPLACE TYPE BODY Count_Substr_Type IS

  --初始化
  STATIC FUNCTION ODCIAggregateInitialize(cs_ctx IN OUT Count_Substr_Type)
    return NUMBER IS
  BEGIN
    --初始化参数   
    cs_ctx := Count_Substr_Type(to_clob(','),',',TYPE_STR());
    RETURN ODCICONST.SUCCESS;
  
  END;

  -- 迭代 ，利用数组去重
  member FUNCTION ODCIAggregateIterate(self     IN OUT Count_Substr_Type,
                                       curvalue IN VARCHAR2) return NUMBER IS
    sub_str   varchar2(500);
    all_len   number ;
    p_len     number ;
    s_len     number ;
  begin
  
     all_len  := length(curvalue);
     s_len:=1;
     p_len:=1;
     sub_str:='';
    while s_len <= all_len loop
       
        p_len := instr(curvalue, splitstr, s_len);
        if p_len = 0 then
          p_len := all_len;
          sub_str := substr(curvalue, s_len);
        else
          sub_str := substr(curvalue, s_len, p_len - s_len);
        end if;
        s_len   := p_len + 1;
       
        if sub_str MEMBER of self.str_array then
          continue;
        else
          self.str_array.extend;
          self.str_array(self.str_array.count):= sub_str;
        end if;
      
      end loop;
    RETURN ODCICONST.SUCCESS;
  
  END;
  
  --合并，可用与并发操作
  member FUNCTION ODCIAggregateMerge(self IN OUT Count_Substr_Type,
                                     ctx2 IN OUT Count_Substr_Type)
    return NUMBER IS
  BEGIN
    
    RETURN ODCICONST.SUCCESS;
  
  END;
  
  --终止，返回数组个数
  member FUNCTION ODCIAggregateTerminate(self        IN OUT Count_Substr_Type,
                                         returnValue OUT number,
                                         flags       IN NUMBER) return NUMBER IS
  BEGIN
  
  
    returnValue:=self.str_array.count;
  
    RETURN ODCICONST.SUCCESS;
  
  END;

END;

3.创建自定义函数（Function）

CREATE OR REPLACE FUNCTION Count_Substr (in_str clob) RETURN number AGGREGATE USING Count_Substr_Type;

4.使用示例

select count_substr('PA001,PA002,PA005,PA003,PA003,PA003,PA001') from dual;
>> 4

---

参考文档

[1].User-Defined Aggregate Functions
[2].Oracle自定义聚集函数