- 글쓴시간
- 분류 기술,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
