User-defined Aggregate Function in Oracle
Author Book Author Books--------+-------- --------+--------poly | A poly | A,B,Cpoly | B amy | D,Epoly | C ====>amy | Damy | EHOW?
select author, to_list(book) as booksfrom table_namegroup by author;
SO SIMPLE?
Yes, we just need to create a user-defined aggregate function as follows:
1. create type object
create or replace type tolist as object ( list varchar2(2000), static function ODCIAggregateInitialize ( actx in out tolist ) return number, member function ODCIAggregateIterate ( selfin out tolist , value in varchar2 ) return number, member function ODCIAggregateTerminate ( self in tolist , returnValueout varchar2, flags in varchar2 ) return number, member function ODCIAggregateMerge (self in out tolist , ctx2 in tolist ) return number )
2. create type body
create or replace type body tolist is static function ODCIAggregateInitialize ( actx in out tolist) return number is begin actx := tolist(''); return ODCIConst.Success; end; member function ODCIAggregateIterate ( selfin out tolist, value in varchar2) return number is begin if self.list is null then self.list := value; else self.list := self.list || ',' || value; end if; return ODCIConst.Success; end;member function ODCIAggregateTerminate ( self in tolist, returnValue out varchar2, flags in varchar2 ) return number is begin returnValue:= self.list; return ODCIConst.Success; end; member function ODCIAggregateMerge (self in out tolist, ctx2 in tolist ) return number is begin if ctx2.list <> '' then self.list := self.list || ',' || ctx2.list; end if; return ODCIConst.Success; end; end;
3. create function
CREATE OR REPLACE FUNCTION to_list( x varchar2) RETURN varchar2PARALLEL_ENABLEAGGREGATE USING tolist;
done http://www.agoit.com/bbs/../../../images/smiles/icon_biggrin.gif
---------------------------------------------
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1005029
http://www.oracle.com/technology/oramag/oracle/06-jul/o46sql.html
==========================
Supplement
"Displaying multiple records in one row" give a summary :
1. SELECT wmsys.wm_concat(dname) departments FROM dept; (10g)
2. A way to get around the restriction that user-defined aggregates may only have one argument, which allows you to specify an alternative separator character.
3. A really delicate way using just sql
SELECT deptno , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenatedFROM ( SELECT deptno , ename , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp )GROUP BY deptnoCONNECT BY prev = PRIOR curr AND deptno = PRIOR deptnoSTART WITH curr = 1;4. Another approach involves harnessing the dark power of XML
SELECT deptno , RTRIM ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()') , ',' ) AS concatenatedFROM empGROUP BY deptno;
页:
[1]