Oracle10g自定义聚合函数（字符串拼接）

    * 自定义聚合函数 wmsys.wm_concat 替换办法
    * 超大字符串拼接，单个字符串4000、分隔符100，可拼出超4000的超长字符串
    * 可自定义指定分隔符separator
    * 可自定义指定排序字段sequence，对于数字或日期类型的字段，先转为字符串，比如to_char(line, 'FM000')
    * 由于聚合函数只支持一个参数，这里使用对象类型实现传入多个参数
    * Oracle11g Release2版本引入了LISTAGG 函数，使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序

select strConcat (strConcat_obj (text , '', to_char(line, 'FM00000')))
from user_source
where name = 'SUBSTRX'
------------------------------------------------------------------------------------------------------------

--定义对象，排序字段名（key）、需拼接的字符串字段名（value）、分隔符（separator）
CREATE OR REPLACE type STRCONCAT_OBJ as object(
value varchar2 (4000),
separator varchar2( 100),
sequence varchar2 (100)
);
/
--定义集合类型的type
CREATE OR REPLACE type STRCONCAT_ARRAY is table of STRCONCAT_OBJ ;
/
--实现聚合函数的接口
CREATE OR REPLACE TYPE STRCONCAT_TYPE AS OBJECT(
  vStr STRCONCAT_ARRAY,
  STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT STRCONCAT_TYPE ) RETURN NUMBER ,
  MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT STRCONCAT_TYPE ,VALUE IN STRCONCAT_OBJ ) RETURN  NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT STRCONCAT_TYPE ,ctx2 IN STRCONCAT_TYPE) RETURN NUMBER ,
  MEMBER FUNCTION ODCIAggregateTerminate (SELF IN STRCONCAT_TYPE , returnValue OUT clob , flags IN NUMBER ) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY STRCONCAT_TYPE
IS
STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT STRCONCAT_TYPE ) RETURN NUMBER
IS
BEGIN
   sctx := STRCONCAT_TYPE (STRCONCAT_ARRAY ());
   RETURN ODCIConst .Success ;
END;

MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT STRCONCAT_TYPE , VALUE IN STRCONCAT_OBJ) RETURN NUMBER
IS
BEGIN
   vStr .EXTEND;
   vStr (vStr .COUNT) := VALUE;
   RETURN ODCIConst .Success ;
END;

MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT STRCONCAT_TYPE ,ctx2 IN STRCONCAT_TYPE) RETURN NUMBER
IS
BEGIN
  for i in 1 .. ctx2 .vStr .count loop
    vStr .extend;
    vStr (vStr .count) := ctx2 .vStr (i );
  end loop;
  RETURN ODCIConst .Success ;
END;

MEMBER FUNCTION ODCIAggregateTerminate (SELF IN STRCONCAT_TYPE , returnValue OUT clob , flags IN NUMBER ) RETURN NUMBER
IS
   tmp_vStr VARCHAR2( 4000 );
   ncount number;
BEGIN
  SELECT count(*) into ncount FROM TABLE(vStr );
  DBMS_LOB.CREATETEMPORARY ( returnValue , TRUE);
  FOR x IN (SELECT rownum, value, separator FROM TABLE(vStr ) order by sequence) LOOP
     if x.rownum = ncount then
        DBMS_LOB.APPEND( returnValue, x.value);
     else
        DBMS_LOB.APPEND( returnValue, x.value|| x. separator);
     end if;
  END LOOP;
  RETURN ODCIConst .Success ;
END;
END;
/
CREATE OR REPLACE FUNCTION STRCONCAT (input STRCONCAT_OBJ ) RETURN clob PARALLEL_ENABLE AGGREGATE USING STRCONCAT_TYPE;
/