自定义函数替代WMSYS.WM_CONCAT

最新推荐文章于 2021-04-02 23:34:44 发布

原创最新推荐文章于 2021-04-02 23:34:44 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

本文介绍如何在Oracle PL/SQL中创建自定义的字符串聚合函数，包括使用VARCHAR2类型和CLOB类型的实例。通过提供的完整代码示例，展示了如何实现ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateTerminate和ODCIAggregateMerge成员函数，并演示了如何在SQL查询中应用此函数。

参考文章：

http://docs.oracle.com/cd/B13789_01/appdev.101/b10800/dciaggfns.htm#i1005028

http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html

提供如下完整代码，经测试可以运行：

create or replace type MyConcatImpl as object
(
  str VARCHAR2(32767), -- second highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT MyConcatImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT MyConcatImpl, 
    value IN VARCHAR2) return number,
  member function ODCIAggregateTerminate(self IN MyConcatImpl, 
    returnValue OUT VARCHAR2, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT MyConcatImpl, 
    ctx2 IN MyConcatImpl) return number
);
/

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

member function ODCIAggregateIterate(self IN OUT MyConcatImpl, value IN VARCHAR2) return number is
begin
  if self.str is not null then
    self.str := self.str || ';' || value;
  else
    self.str := value;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN MyConcatImpl, returnValue OUT VARCHAR2, flags IN number) return number is
begin
  returnValue := self.str;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT MyConcatImpl, ctx2 IN MyConcatImpl) return number is
begin
  if ctx2.str is not null then
    self.str := self.str || ';' || ctx2.str;
  end if;
  return ODCIConst.Success;
end;
end;
/
create or replace FUNCTION MyConcat(input VARCHAR2) RETURN VARCHAR2 
PARALLEL_ENABLE AGGREGATE USING MyConcatImpl;
/

如执行如下SQL：

select MyConcat(to_char(tt.user_id || ',' || tt.username )) as concat_char from dba_users tt

注：如果要处理的字符串很长，则可将VARCHAR2替换成Clob类型。