关于ORACLE wmsys.wm_concat 行转列函数使用

    

转自 http://blog.youkuaiyun.com/pengdingxu10/article/details/46438567

这两天在项目中遇到了一个问题，就是系统中使用了wmsys.wm_concat引发的相关问题，PM也提议不要使用，原因有下：

1.该函数不是Oracle公开的系统函数，它的用户是wmsys,而不是sys或者system，oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户，这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。

2.大量使用这个函数也会导致临时表空间爆满，这是因为在10.2.0.5中，使用wmsys.wm_concat返回的结果格式是CLOB，CLOB占用的临时表空间只有在连接释放后才会释放，部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大，会导致临时表空间爆满的故障

3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁，可能会导致某些对象被锁。

解决办法：

官方文档给了建议，可以参考该函数自己建立一个函数实现相同的行列转换功能。

然后我上网找了一些资料，http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html

确实可以实现跟wmsys.wm_concat一样的功能，下面是这篇文章的实现

create or replace TYPE en_concat_im

   AUTHID CURRENT_USER AS OBJECT

   (

      CURR_STR VARCHAR2(32767),

     STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im) RETURN NUMBER,

     MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,

       P1 IN VARCHAR2) RETURN NUMBER,

     MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,

          RETURNVALUE OUT VARCHAR2,

        FLAGS IN NUMBER)

      RETURN NUMBER,

    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,

    SCTX2 IN  en_concat_im) RETURN NUMBER

 );

  
create or replace TYPE BODY en_concat_im

   IS

     STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT en_concat_im)

     RETURN NUMBER

     IS

     BEGIN

    SCTX := en_concat_im(NULL) ;

    RETURN ODCICONST.SUCCESS;

   END;

   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT en_concat_im,

          P1 IN VARCHAR2)

     RETURN NUMBER

    IS

    BEGIN

    IF(CURR_STR IS NOT NULL) THEN

     CURR_STR := CURR_STR || ';' || P1;

    ELSE

      CURR_STR := P1;

    END IF;

    RETURN ODCICONST.SUCCESS;

     END;

    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN en_concat_im,

          RETURNVALUE OUT VARCHAR2,

         FLAGS IN NUMBER)

   RETURN NUMBER

    IS

     BEGIN

   RETURNVALUE := CURR_STR ;

    RETURN ODCICONST.SUCCESS;

    END;

     MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT en_concat_im,

    SCTX2 IN en_concat_im)

     RETURN NUMBER

     IS

    BEGIN

    IF(SCTX2.CURR_STR IS NOT NULL) THEN

      SELF.CURR_STR := SELF.CURR_STR || ';' || SCTX2.CURR_STR ;

    END IF;

   RETURN ODCICONST.SUCCESS;

   END;

  END;

create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING en_concat_im ;

然后运行测试：select en_concat(username) from dba_users;   ok可以实现行列转换。具体的性能分析可以浏览该网址的文章

但是我在运行的时候出了个问题，就是查询的表数据量比较大的时候会报：Oracle ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小这个错误

我当时不明白什么原因，后面百度了一下这个原因，看到了一篇文章：http://bbs.csdn.NET/topics/360059765

楼主的问题：

使用wmsys.wm_concat多列合成一列遇到问题
ORA-22813: 操作数值超出系统的限制

往下看了之后对比了一下这个问题的解决办法，原理跟上面一样，也是自定义type来实现，但是有一个不同点的就是，上面的实现我标注的地方，返回的是varchar2类型的

在看看这篇文章里面的实现：

CREATE  OR  REPLACE  TYPE zh_concat_im

AUTHID  CURRENT_USER  AS  OBJECT

(

   CURR_STR clob,

   STATIC  FUNCTION  ODCIAGGREGATEINITIALIZE(SCTX  IN  OUT  zh_concat_im)  RETURN  NUMBER,

   MEMBER  FUNCTION  ODCIAGGREGATEITERATE(SELF  IN  OUT  zh_concat_im,

   P1  IN  VARCHAR2)  RETURN  NUMBER,

   MEMBER  FUNCTION  ODCIAGGREGATETERMINATE(SELF  IN  zh_concat_im,

   RETURNVALUE OUT clob,

   FLAGS  IN  NUMBER)

   RETURN  NUMBER,

   MEMBER  FUNCTION  ODCIAGGREGATEMERGE(SELF  IN  OUT  zh_concat_im,

   SCTX2  IN  zh_concat_im)  RETURN  NUMBER

);

 

CREATE  OR  REPLACE  TYPE BODY zh_concat_im

IS

   STATIC  FUNCTION  ODCIAGGREGATEINITIALIZE(SCTX  IN  OUT  zh_concat_im)

   RETURN  NUMBER

   IS

   BEGIN

   SCTX := zh_concat_im( NULL ) ;

   RETURN  ODCICONST.SUCCESS;

   END ;

   MEMBER  FUNCTION  ODCIAGGREGATEITERATE(SELF  IN  OUT  zh_concat_im,

 

   P1  IN  VARCHAR2)

   RETURN  NUMBER

   IS

   BEGIN

   IF(CURR_STR  IS  NOT  NULL )  THEN

   CURR_STR := CURR_STR ||  ','  || P1;

   ELSE

   CURR_STR := P1;

   END  IF;

   RETURN  ODCICONST.SUCCESS;

   END ;

   MEMBER  FUNCTION  ODCIAGGREGATETERMINATE(SELF  IN  zh_concat_im,

   RETURNVALUE OUT clob,

   FLAGS  IN  NUMBER)

   RETURN  NUMBER

   IS

   BEGIN

   RETURNVALUE := CURR_STR ;

   RETURN  ODCICONST.SUCCESS;

   END ;

   MEMBER  FUNCTION  ODCIAGGREGATEMERGE(SELF  IN  OUT  zh_concat_im,

   SCTX2  IN  zh_concat_im)

   RETURN  NUMBER

   IS

   BEGIN

   IF(SCTX2.CURR_STR  IS  NOT  NULL )  THEN

   SELF.CURR_STR := SELF.CURR_STR ||  ','  || SCTX2.CURR_STR ;

   END  IF;

   RETURN  ODCICONST.SUCCESS;

   END ;

END ;

create  or  replace  FUNCTION  zh_concat(P1 VARCHAR2)

RETURN  clob AGGREGATE USING zh_concat_im ;

我有类似的问题，看到好文一定要转~~