按照指定的顺序对字段值进行连接--聚合函数的使用

字符串连接，每次想到使用的就是wmsys.wm_concat()函数，这个函数是oracle内部使用的，所以一般不推介使用在程序中。

例如在11gR1的时候 wmsys.wm_concat()的返回值是string,但在wmsys.wm_concat()返回时就变成了clob.  如果想去除重复还能用wmsys.wm_concat(distinct columnName)

这个默认是用逗号做连接符的，所以如果想使用别的连接符，就需要自定义函数了。

这里就需要用到聚合函数：

客户提出要求需要使用换行符来展示，聚合函数如下。但这里是直接对field进行了distinct操作的，代码如下：

CREATE OR REPLACE TYPE "STR_LINK"                                                                          AS OBJECT (
  O_STR VARCHAR2(32767),
  EXIST_FLAG CHAR(1),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STR_LINK) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_LINK, CTX2 IN STR_LINK) RETURN NUMBER
 )

CREATE OR REPLACE TYPE BODY STR_LINK IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STR_LINK) RETURN NUMBER IS
  BEGIN
    SCTX := STR_LINK(NULL,'0');
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF SELF.EXIST_FLAG = '0' AND LENGTHB(SELF.O_STR) > 0 THEN
      IF INSTR(chr(13) || SELF.O_STR || chr(13),chr(13) || VALUE || chr(13)) = 0 THEN
        Exist_flag := '1';
      END IF;
    END IF;
    SELF.O_STR := SELF.O_STR || VALUE || chr(13);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
  BEGIN
    IF SELF.EXIST_FLAG = '0' THEN
      RETURNVALUE := SUBSTR(SELF.O_STR, 1,INSTR(SELF.O_STR, chr(13))-1);
    ELSE
      RETURNVALUE := SUBSTR(SELF.O_STR, 1, LENGTH(SELF.O_STR) - 1);
    END IF;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_LINK, CTX2 IN STR_LINK) RETURN NUMBER IS
  BEGIN
    NULL;
    RETURN ODCICONST.SUCCESS;
  END;
END;

--创建相关的函数

CREATE OR REPLACE FUNCTION STRLINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING STR_LINK;

 

当然可以根据需求做细微调整，例如处理clob字段。

 

但是客户的要求更加具体，他们希望按照自己的意愿来对字符进行连接，并且连接串是否去重复。另外一个就是在sql语句中，使用了order by语句。但在做连接的时候，出来的结果并不是按照之前order by查出来的顺序进行连接的，有点混乱。至此，我不得不继续完善聚合函数。

在网上一番搜索后，终于得到启发,代码如下：

--入参类型

CREATE OR REPLACE TYPE "STRINGORDER"                                                                          as object (
TheString  varchar2(4000) ,
TheOrder   number(19),
IsDistinct char(1));

-- 聚合函数

CREATE OR REPLACE TYPE "STRINGAGGTYPE2"                                                                          as object
(
   theString clob,
   MaxOrder  number(19),
   static function
        ODCIAggregateInitialize(sctx IN OUT StringAggType2 )
        return number,
   member function
        ODCIAggregateIterate(self IN OUT StringAggType2 ,
                             value IN StringOrder )
        return number,
   member function
        ODCIAggregateTerminate(self IN StringAggType2,
                               returnValue OUT  clob,
                               flags IN number)
        return number,
   member function
        ODCIAggregateMerge(self IN OUT StringAggType2,
                           ctx2 IN StringAggType2)
        return number
);

create or replace type body StringAggType2
 is
 static function ODCIAggregateInitialize(
   sctx IN OUT StringAggType2)
 return number
 is
 begin
     sctx := StringAggType2( null,0 );
     return ODCIConst.Success;
 end;
 member function ODCIAggregateIterate(
   self IN OUT StringAggType2 ,
   value IN StringOrder )
 return number
 is
 begin
   if value.IsDistinct = 'Y' then
     IF instr(chr(13) || self.theString || chr(13),chr(13) ||value.TheString || chr(13)) > 0 THEN
        return ODCIConst.Success;
      END IF;
   end if;
   if value.TheOrder > self.MaxOrder then
     self.theString := self.theString ||
                       lpad(chr(13),value.TheOrder-self.MaxOrder,chr(13)) ||
                       value.TheString ;
     self.MaxOrder := value.TheOrder ;
   elsif value.TheOrder < self.MaxOrder then
     self.theString := substr(self.theString,1,instr(self.theString,chr(13),1,value.TheOrder))||
                       value.TheString ||
                       substr(self.theString,instr(self.theString,chr(13),1,value.TheOrder)+1) ;
   end if;
   return ODCIConst.Success;
 end;
 member function ODCIAggregateTerminate(
   self IN StringAggType2,
   returnValue OUT  clob,
   flags IN number)
 return number
 is
  ls_tmp clob;
 begin
   ls_tmp := self.theString;
   for i in 1..self.MaxOrder loop
     if instr(ls_tmp,chr(13)||chr(13),1)>0 then
       ls_tmp := replace(ls_tmp,chr(13)||chr(13),chr(13));
     else
       exit;
     end if;
   end loop;
   returnValue := rtrim( ltrim(ls_tmp,chr(13) ),chr(13));
   return ODCIConst.Success;
 end;
 member function ODCIAggregateMerge(
   self IN OUT StringAggType2,
   ctx2 IN StringAggType2)
 return number
 is
 begin
     self.theString := self.theString || chr(13) || ctx2.theString;
     self.MaxOrder := self.MaxOrder + ctx2.MaxOrder ;
     return ODCIConst.Success;
 end;
end ;

--相关函数创建

CREATE OR REPLACE FUNCTION StrAggOrd(input StringOrder)
RETURN clob
PARALLEL_ENABLE AGGREGATE USING StringAggType2;

 

使用示例：

select StrAggOrd(StringOrder(nvl(to_char(columnName), 'N/A'), rn, 'N')) AAA ,row_number() over(partition by columnB order by columnC) rn

 from tableName;

可见聚合函数可以解决问题，只要好好的定义入参的type就行。