拼字符串 将多行拼成一行

create or replace type string_agg_type as object
(
   total varchar2(4000),

   static function
        ODCIAggregateInitialize(sctx IN OUT string_agg_type )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT string_agg_type ,
                             value IN varchar2 )
        return number,

   member function
        ODCIAggregateTerminate(self IN string_agg_type,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT string_agg_type,
                           ctx2 IN string_agg_type)
        return number
);
/

create or replace type body string_agg_type
 is
 
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
    sctx := string_agg_type( null );
    return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                     value IN varchar2 )
return number
is
begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
                                       returnValue OUT varchar2,
                                       flags IN number)
return number
is
begin
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type)
return number
is
begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
end;


end;
/
 
 
CREATE or replace                                    
FUNCTION stragg(input varchar2 )                
RETURN varchar2                                 
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/                                               

 

评论

2 楼 xiaolobster 2011-11-17  

我想问一下  上面这个函数，如果字符串超长了如何调整？拼成的字符串最长多少？

1 楼 DataBird 2010-11-10  

非常感谢你无私的奉献，这个函数太棒了！！！
拿来可以直接用。
每行末尾都自动加了逗号，最后一行却没有加，很细致！
例如：SELECT KPI_CH FROM WY_KPI WHERE KPI_CH LIKE '%TCH%'
KPI_CH
TCH话务量
TCH每线话务量
TCH可用信道数
TCH掉话次数_射频
TCH掉话次数_切换
TCH掉话次数_含切换
TCH占用次数_不含切换
TCH占用次数_切换
TCH占用次数_含切换
TCH掉话率_含切换
TCH拥塞次数_含切换
TCH尝试次数_含切换
TCH拥塞率_含切换
SELECT STRAGG(KPI_CH) FROM WY_KPI WHERE KPI_CH LIKE '%TCH%'
执行您的函数后：
TCH话务量,TCH每线话务量,TCH可用信道数,TCH掉话次数_射频,TCH掉话次数_切换,TCH掉话次数_含切换,TCH占用次数_不含切换,TCH占用次数_切换,TCH占用次数_含切换,TCH掉话率_含切换,TCH拥塞次数_含切换,TCH尝试次数_含切换,TCH拥塞率_含切换
THANKS!!!