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

------------------------------------------- -- 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,不便于移植啊！