對字串的“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]