对字符串的“sum”——在Oracle中自定义聚集函数的例子

oracle自定义聚集函数接口简介 
   a. static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type )    
               return number 
   自定义聚集函数初始化设置,从这儿开始一个聚集函数 
   b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2) 
               return number 
    自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联 
   c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT  varchar2,flags IN number) 
             return number 
     用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候. 
    d. member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number) 
     终止聚集函数的处理,返回聚集函数处理的结果. 

2. 实现的例子. 
[code] 
create type strcat_type as object ( 
    cat_string varchar2(4000), 
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number, 
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number, 
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number, 
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number 
) 
/ 

create type body strcat_type is 
  static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number 
  is 
  begin 
      cs_ctx := strcat_type( null ); 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateIterate(self IN OUT strcat_type, 
                                       value IN varchar2 ) 
  return number 
  is 
  begin 
      self.cat_string := self.cat_string || ','|| value; 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateTerminate(self IN Out strcat_type, 
                                         returnValue OUT varchar2, 
                                         flags IN number) 
  return number 
  is 
  begin 
      returnValue := ltrim(rtrim(self.cat_string,','),','); 
      return ODCIConst.Success; 
  end; 

  member function ODCIAggregateMerge(self IN OUT strcat_type, 
                                     ctx2 IN Out strcat_type) 
  return number 
  is 
  begin 
      self.cat_string := self.cat_string || ',' || ctx2.cat_string; 
      return ODCIConst.Success; 
  end; 

end; 
/ 

CREATE or replace 
FUNCTION strcat(input varchar2 ) 
RETURN varchar2 
PARALLEL_ENABLE AGGREGATE USING strcat_type; 
/ 

[/code] 


3. 具体应用, 
[code] 
15:16:52 SQL> select empno,ename,deptno,job from scott.emp; 

     EMPNO ENAME          DEPTNO JOB 
---------- ---------- ---------- --------- 
      7369 SMITH              20 CLERK 
      7499 ALLEN              30 SALESMAN 
      7521 WARD               30 SALESMAN 
      7566 JONES              20 MANAGER 
      7654 MARTIN             30 SALESMAN 
      7698 BLAKE              30 MANAGER 
      7782 CLARK              10 MANAGER 
      7788 SCOTT              20 ANALYST 
      7839 KING               10 PRESIDENT 
      7844 TURNER             30 SALESMAN 
      7876 ADAMS              20 CLERK 
      7900 JAMES              30 CLERK 
      7902 FORD               20 ANALYST 
      7934 MILLER             10 CLERK 

14 rows selected. 

Elapsed: 00:00:00.01 

15:18:29 SQL> col dept_emplist format a60 
15:18:41 SQL> select deptno,strcat(empno||'-'||ename) dept_emplist 
15:19:01   2  from scott.emp group by deptno; 

    DEPTNO DEPT_EMPLIST 
---------- ------------------------------------------------------------ 
        10 7782-CLARK,7839-KING,7934-MILLER 
        20 7369-SMITH,7902-FORD,7876-ADAMS,7788-SCOTT,7566-JONES 
        30 7499-ALLEN,7698-BLAKE,7654-MARTIN,7844-TURNER,7900-JAMES,7521-WARD 

Elapsed: 00:00:00.04 
15:19:08 SQL> col job_emplist format a80 
15:19:23 SQL> select job,strcat(empno||'-'||ename) job_emplist 
15:19:43   2  from scott.emp group by job; 

JOB       JOB_EMPLIST 
--------- -------------------------------------------------------------------------------- 
ANALYST   7788-SCOTT,7902-FORD 
CLERK     7369-SMITH,7900-JAMES,7876-ADAMS,7934-MILLER 
MANAGER   7566-JONES,7782-CLARK,7698-BLAKE 
PRESIDENT 7839-KING 
SALESMAN  7499-ALLEN,7521-WARD,7844-TURNER,7654-MARTIN 

Elapsed: 00:00:00.03 
15:19:50 SQL> 
[/code] 

[ Last edited by jametong on 2004-10-22 at 10:37 ]

---

jametong

本文参考了oracle数据挖掘的文档  

[url]http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1004572[/url]