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

http://blog.itpub.net/post/468/3380


另外:
 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]