--该sql生成了一个名为list的函数。功能是将多行的数据记录转换成一行的记录,各数据用“参数的第一位字符隔开”分隔。
--使用样例:select ColumnToRow(','||Tname) from tab where rownum<10;
create or replace type list_agg as object
(
total varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT list_agg )
return number,
member function
ODCIAggregateIterate(self IN OUT list_agg ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN list_agg,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT list_agg,
ctx2 IN list_agg)
return number
);
/
create or replace type body list_agg
is
static function ODCIAggregateInitialize(sctx IN OUT list_agg)
return number
is
begin
sctx := list_agg(null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT list_agg,
value IN varchar2 )
return number
is
begin
self.total := self.total||value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN list_agg,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := substr(self.total,2);
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT list_agg,
ctx2 IN list_agg)
return number
is
begin
self.total := self.total||ctx2.total;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION ColumnToRow(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING list_agg;
/
select ColumnToRow(','||Tname) from tab where rownum<10;
select * from users;
--使用样例:select ColumnToRow(','||Tname) from tab where rownum<10;
create or replace type list_agg as object
(
total varchar2(4000),
static function
ODCIAggregateInitialize(sctx IN OUT list_agg )
return number,
member function
ODCIAggregateIterate(self IN OUT list_agg ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN list_agg,
returnValue OUT varchar2,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT list_agg,
ctx2 IN list_agg)
return number
);
/
create or replace type body list_agg
is
static function ODCIAggregateInitialize(sctx IN OUT list_agg)
return number
is
begin
sctx := list_agg(null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT list_agg,
value IN varchar2 )
return number
is
begin
self.total := self.total||value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN list_agg,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := substr(self.total,2);
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT list_agg,
ctx2 IN list_agg)
return number
is
begin
self.total := self.total||ctx2.total;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION ColumnToRow(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING list_agg;
/
select ColumnToRow(','||Tname) from tab where rownum<10;
select * from users;