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;
/
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值