1.聚合函数法。
/*TYPE*/
create or replace type strcat_type as object
(
currentstr varchar2(4000),
currentseprator varchar2(8),
static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,
member functioan ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,
member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number
);
create or replace type body strcat_type is
static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is
begin
sctx := strcat_type('',',');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is
begin
if self.currentstr is null then
self.currentstr := value;
else
self.currentstr := self.currentstr ||currentseprator || value;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is
begin
returnValue := self.currentstr;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is
begin
if ctx2.currentstr is null then
self.currentstr := self.currentstr;
elsif self.currentstr is null then
self.currentstr := ctx2.currentstr;
else
self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;
end if;
return ODCIConst.Success;
end;
end;
/*函数*/
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;
例：SELECT t.id,strcat(t.name) FROM t_avg t GROUP BY t.id
2.listagg函数法。（oracle 11gr2及以上版本）
例： select id,listagg(name,',') within group(order by id) from t_avg t group by id;
评论回复