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 ]
