拼字符串将多行拼成一行

最新推荐文章于 2024-02-04 19:51:13 发布

原创最新推荐文章于 2024-02-04 19:51:13 发布 · 398 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#c# #java

oracle 专栏收录该内容

39 篇文章

订阅专栏

本文详细介绍了SQL中字符串聚合函数的实现原理，包括初始化、迭代、终止和合并四个阶段，以及如何在实际场景中应用这些函数进行数据汇总。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;
/