oralce函数wm_concat 替代办法

来源：互联网发布：中国输电网络先进吗编辑：程序博客网时间：2024/06/06 03:29

原帖地址：http://blog.csdn.net/pengdingxu10/article/details/46438567

oracle11g后推荐使用listagg函数。

vm_concat

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

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

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

解决办法：

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

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 ;

这个定义的的clob类型，所有这个函数可以实现，没有出现了Oracle ORA-06502:这个错误，顿时恍然啊。

至于clob这个类型跟varchar2各位就查资料看看了，但是目前我还是有点不明白，这样实现也是用clob这个类型，会不会也会临时表空间增大，目前没有测试过。

阅读全文

0 0