윈디하나의 누리사랑방. 이런 저런 얘기

글쓴시간
분류 기술,IT

오라클에서 Aggregation Function(집합 함수)만드는 예제.

사용자 삽입 이미지

필요해서 여기 써 놓는다. 혹시라도 잊어버릴까봐. 참고로 오라클 11 R2 부터는 아래 함수 대신 LISTAGG 를 사용할 수 있다.

create or replace type taggcat as object (
  str varchar2(4000),
  static function ODCIAggregateInitialize(sctx in out taggcat) return number,
  member function ODCIAggregateIterate   (self in out taggcat, value in varchar2) return number,
  member function ODCIAggregateTerminate (self in     taggcat, returnValue out varchar2, flags in number) return number,
  member function ODCIAggregateMerge     (self in out taggcat, ctx2 in taggcat) return number
);
/
create or replace type body taggcat is
  static function ODCIAggregateInitialize(sctx in out taggcat) return number
  is
  begin
    sctx := taggcat(null);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate   (self in out taggcat, value in varchar2) return number
  is
  begin
    str := str || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate (self in     taggcat, returnValue out varchar2, flags in number) return number
  is
  begin
    returnValue := RTRIM(LTRIM(SELF.str, ','), ',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge     (self in out taggcat, ctx2 in taggcat) return number
  is
  begin
    str := str || ctx2.str;
    return ODCIConst.Success;
  end;
end;
/
create or replace function aggcat (input varchar2) return varchar2
parallel_enable aggregate using taggcat;
/


실행예)

SQL> select NAME, NICKNAME from example;
NAME       NICKAME
---------- ----------
JAMES      J'
WINDY      HANA
WINDY      JEN

SQL> select NAME, aggcat(NICKNAME) NICKNAMES from example group by NAME;
NAME       NICKAMES
---------- ----------
JAMES      J'
WINDY      HANA,JEN