10g/9i에서 LINKAGG 함수를 만들어보자

ASK TOM_Oracle : [멋져부러~]

9i:

SELECT deptno, CURSOR(SELECT * FROM emp where deptno=dept.deptno) nested_TABLE
  FROM dept;

-- User defined agregate through pipelined function 
CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
  RETURN  VARCHAR2 IS
    ret VARCHAR2(32000);
    tmp VARCHAR2(4000);
BEGIN
    loop
        fetch cur into tmp;
        exit when cur%NOTFOUND;
            ret := ret || ',' || tmp;
    end loop;
    RETURN ret;
END;
/

SELECT distinct
    deptno,
    SUBSTR(CONCAT_LIST(CURSOR(
        SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
    )),1,40) employees
FROM emp e;

Followup   November 28, 2001 - 2pm Central time zone:

Or, even better, in 9i -- use a user defined aggregate so you don't have run the concat_list 
function once per row and then through out the dups (it would be better to write:

  select deptno, SUBSTR(CONCAT_LIST(CURSOR(
        SELECT ename FROM emp ee WHERE e.deptno = ee.deptno
    )),1,40) employees
    from ( select distinct deptno from emp );

)


But a good general purpose solution is:


scott@ORA9I.WORLD> create or replace type StringAggType as object
  2  (
  3     theString varchar2(4000),
  4  
  5     static function
  6          ODCIAggregateInitialize(sctx IN OUT StringAggType )
  7          return number,
  8  
  9     member function
 10          ODCIAggregateIterate(self IN OUT StringAggType ,
 11                               value IN varchar2 )
 12          return number,
 13  
 14     member function
 15          ODCIAggregateTerminate(self IN StringAggType,
 16                                 returnValue OUT  varchar2,
 17                                 flags IN number)
 18          return number,
 19  
 20     member function
 21          ODCIAggregateMerge(self IN OUT StringAggType,
 22                             ctx2 IN StringAggType)
 23          return number
 24  );
 25  /

Type created.

scott@ORA9I.WORLD> 
scott@ORA9I.WORLD> create or replace type body StringAggType
  2  is
  3  
  4  static function ODCIAggregateInitialize(sctx IN OUT StringAggType)
  5  return number
  6  is
  7  begin
  8      sctx := StringAggType( null );
  9      return ODCIConst.Success;
 10  end;
 11  
 12  member function ODCIAggregateIterate(self IN OUT StringAggType,
 13                                       value IN varchar2 )
 14  return number
 15  is
 16  begin
 17      self.theString := self.theString || ',' || value;
 18      return ODCIConst.Success;
 19  end;
 20  
 21  member function ODCIAggregateTerminate(self IN StringAggType,
 22                                         returnValue OUT varchar2,
 23                                         flags IN number)
 24  return number
 25  is
 26  begin
 27      returnValue := rtrim( ltrim( self.theString, ',' ), ',' );
 28      return ODCIConst.Success;
 29  end;
 30  
 31  member function ODCIAggregateMerge(self IN OUT StringAggType,
 32                                     ctx2 IN StringAggType)
 33  return number
 34  is
 35  begin
 36      self.theString := self.theString || ',' || ctx2.theString;
 37      return ODCIConst.Success;
 38  end;
 39  
 40  
 41  end;
 42  /

Type body created.

scott@ORA9I.WORLD> 
scott@ORA9I.WORLD> CREATE or replace
  2  FUNCTION stringAgg(input varchar2 )
  3  RETURN varchar2
  4  PARALLEL_ENABLE AGGREGATE USING StringAggType;
  5  /

Function created.

scott@ORA9I.WORLD> 
scott@ORA9I.WORLD> column enames format a30
scott@ORA9I.WORLD> select deptno, stringAgg(ename) enames
  2    from emp
  3   group by deptno
  4  /

    DEPTNO ENAMES
---------- ------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAME
           S,WARD


scott@ORA9I.WORLD>