自定义oracle聚集函数，类似于功能wm_concat

-------------------------------------------
-- Export file for user BOSS1214         --
-- Created by user on 2011-2-25, 9:34:30 --
-------------------------------------------

spool str_sum_sql.log

prompt
prompt Creating type STR_SUM_OBJ
prompt =========================
prompt
CREATE OR REPLACE TYPE STR_SUM_OBJ AS OBJECT --聚合函数的实质就是一个对象
(
  SUM_STRING VARCHAR2(4000),
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ)
    RETURN NUMBER, --对象初始化

--聚合函数的迭代方法(这是最重要的方法)
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT STR_SUM_OBJ,
                                       VALUE IN VARCHAR2) RETURN NUMBER,

--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF   IN OUT STR_SUM_OBJ,
                                     V_NEXT IN STR_SUM_OBJ)
    RETURN NUMBER,

--终止聚集函数的处理,返回聚集函数处理的结果.
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF         IN STR_SUM_OBJ,
                                         RETURN_VALUE OUT VARCHAR2,
                                         V_FLAGS      IN NUMBER)
    RETURN NUMBER

)
/

prompt
prompt Creating function STR_SUM
prompt =========================
prompt
CREATE OR REPLACE FUNCTION STR_SUM(VALUE VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STR_SUM_OBJ;
/

prompt
prompt Creating type body STR_SUM_OBJ
prompt ==============================
prompt
CREATE OR REPLACE TYPE BODY STR_SUM_OBJ IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ)
    RETURN NUMBER IS
  BEGIN
    V_SELF := STR_SUM_OBJ(NULL);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT STR_SUM_OBJ,
                                       VALUE IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    SELF.SUM_STRING := SELF.SUM_STRING || VALUE;
    RETURN ODCICONST.SUCCESS;
  END ODCIAGGREGATEITERATE;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF   IN OUT STR_SUM_OBJ,
                                     V_NEXT IN STR_SUM_OBJ)
    RETURN NUMBER IS
  BEGIN
    SELF.SUM_STRING := SELF.SUM_STRING || V_NEXT.SUM_STRING;
    RETURN ODCICONST.SUCCESS;
  END ODCIAGGREGATEMERGE;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF         IN STR_SUM_OBJ,
                                         RETURN_VALUE OUT VARCHAR2,
                                         V_FLAGS      IN NUMBER)
    RETURN NUMBER IS
  BEGIN
    RETURN_VALUE := SELF.SUM_STRING;
    RETURN ODCICONST.SUCCESS;
  END ODCIAGGREGATETERMINATE;
END;
/


spool off

 

 

示例用法如下：

select STR_SUM(pi.productcode || ',')
  from t_productinfos pi
 where pi.producttypecode = '00';

 另外，wm_concat函数，在一些oracle版本里可能会返回clob类型，导致程序出错。

因此wm_concat是WMSYS下的东西，oracle内部用的，一般程序中最好不要用到它。

 

 

*********************************************************************************

今天在测试环境上运行一个使用到wmsys.wm_concat函数的过程，日志记录错误为

1	ORA-00932: inconsistent datatypes: expected - got CLOB

但是该过程在生产环境上运行正常不报错。网上google之，该函数为undocumented的，不被oracle官方支持，随着版本变化也会有修改，但并不保证行为一致。

测试环境为

1	Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 compatible string 10.2.0.3.0

重启并修改compatible string 为10.2.0.5.0,测试wmsys.wm_concat返回值仍为CLOB

生产环境为

1	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 compatible string 11.2.0.0.0

最终解决方法是在wm_concat外层用to_char作转换。 PS：之前在11g生产环境中用过一次wm_concat，结果发现在同一个select子句中使用该函数不能超过两次，于是换用listagg和正则表达式解决了问题。看来以后尽量避免使用wmsys.wm_concat,不便于移植啊！