写了一个聚合函数，将字符串连接起来 - Oracle技巧

前几天在cu上看到寻求将字符串连接起来的方法, 想到用一个类似sum等聚合函数的函数可以更简单的实现, 搜索了一下oracle 10g的文挡, 找到个示例, 就依葫芦画瓢的写了一个. 在Rollingpig的blog上看到了这篇文章, 实现了同样的功能, 不过rollingpig是使用函数实现的.
代码如下:

create or replace type connstrImpl as object
   (
      currentstr varchar2(4000),
currentseprator varchar2(8),
      static function ODCIAggregateInitialize(sctx IN OUT connstrImpl)
      return number,
      member function ODCIAggregateIterate(self IN OUT connstrImpl,
      value IN VARCHAR2) return number,
      member function ODCIAggregateTerminate(self IN connstrImpl,
      returnValue OUT VARCHAR2, flags IN number) return number,
      member function ODCIAggregateMerge(self IN OUT connstrImpl,
      ctx2 IN connstrImpl) return number
   );
   /

   create or replace type body connstrImpl is
   static function ODCIAggregateInitialize(sctx IN OUT connstrImpl)
   return number is
   begin
      sctx := connstrImpl('','/');
      return ODCIConst.Success;
   end;
   member function ODCIAggregateIterate(self IN OUT connstrImpl, value IN VARCHAR2) return number is
   begin
      if self.currentstr is null then
      self.currentstr := value;
      else
      self.currentstr := self.currentstr ||currentseprator || value;
      end if;
      return ODCIConst.Success;
   end;
   member function ODCIAggregateTerminate(self IN connstrImpl, returnValue OUT VARCHAR2, flags IN number) return number is
   begin
      returnValue := self.currentstr;
      return ODCIConst.Success;
   end;
   member function ODCIAggregateMerge(self IN OUT connstrImpl, ctx2 IN connstrImpl) return number is
   begin
      if ctx2.currentstr is null then
   self.currentstr := self.currentstr;
elsif self.currentstr is null then
   self.currentstr := ctx2.currentstr;
else
   self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;
end if;
      return ODCIConst.Success;
   end;
   end;
   /

   CREATE OR REPLACE FUNCTION connstr (input VARCHAR2) RETURN VARCHAR2
   PARALLEL_ENABLE AGGREGATE USING connstrImpl;
   /

使用示例如下:

SQL> select connstr(loc) from dept ;

CONNSTR(LOC)
--------------------------------------------------------------------------------
NEW YORK/DALLAS/CHICAGO/BOSTON

参考资料
Oracle® Data Cartridge Developer's Guide 10g Release 1 (10.1) User-Defined Aggregate Functions