---- ================================================================
-- 1. 创建oracle type
-- ================================================================
create or replace type columnstrcat as object
(
str varchar2(4000),
static function odciaggregateinitialize(sctx in out columnstrcat) return number,
member function odciaggregateiterate(self in out columnstrcat, value in varchar2) return number,
member function odciaggregateterminate(self in columnstrcat, returnvalue out varchar2, flags in number) return number,
member function odciaggregatemerge(self in out columnstrcat,ctx2 in columnstrcat) return number
);
-- ================================================================
-- 2. 创建 oracle type bodies
-- ================================================================
create or replace type body columnstrcat is
-- member procedures and functions
static function odciaggregateinitialize(sctx in out columnstrcat)
return number
is
begin
sctx := columnstrcat('');
return odciconst.success;
end;
member function odciaggregateiterate(self in out columnstrcat, value in varchar2) return number is
counter integer;
begin
self.str := self.str||value||',';
return odciconst.success;
end;
member function odciaggregateterminate(self in columnstrcat, returnvalue out varchar2, flags in number) return number is
begin
returnvalue := substr(self.str,1,length(self.str)-1);
return odciconst.success;
end;
member function odciaggregatemerge(self in out columnstrcat, ctx2 in columnstrcat) return number is
begin
self.str := self.str||ctx2.str;
return odciconst.success;
end;
end;
-- -- ================================================================
-- 3. 创建 oracle function
-- -- ================================================================
create or replace function columnstrjoin (input varchar2) return varchar2
parallel_enable aggregate using columnstrcat;
-- -- ================================================================
-- 4. 使用示例
-- -- ================================================================
select columnstrjoin(a.name) from table_name a where a.type > 1