wmsys.wm_concat 所存在危险 + 函数自定义——代码

来源：互联网发布：淘宝匡威正品店编辑：程序博客网时间：2024/06/16 05:22

本文讲述了，Oracle数据库通过wmsys.wm_concat进行行列转换的危险性。
wmsys工作实景管理的帐户（Oracle Workspace Manager）
在网上流传很多文章，描述了使用wmsys.wm_concat对象实现行列转换的方法，这种方法不被Oracle所推荐，因为WMSYS用户用于Workspace Manager，其函数对象可能因版本而不同，这种变化在11.2.0.3及10.2.0.5中体现出来。原本WM_CONCAT函数返回值为VARCHAR2变更为CLOB。这一变化导致了很多程序的异常。

该函数可以实现行列转换：

SQL> select wmsys.wm_concat(username) from dba_users;         WMSYS.WM_CONCAT(USERNAME)----------------         SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM

在11.2.0.3中，其返回值类型变更为CLOB：

SQL> desc wmsys.wm_concatFUNCTION wmsys.wm_concat RETURNS CLOB Argument Name          Type            In/Out Default? ------------------------------ ----------------------- ------ -------- P1             VARCHAR2        IN

Oracle建议用户使用自定义函数来实现该功能，而不是使用WorkSpace的这个内部函数。
这个函数包含一个Type、Type Body、Function，可以参考Oracle的实现方式来实现这个函数。

请注意，在将数据库从其他版本升级到10.2.0.5和11.2.0.3中时，必须注意到，这个函数的返回值类型变化。

例如如下一个系列的函数，可以帮助用户构建自有的行列转换函数：

SQL> 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      );      /Type created.    SQL>     SQL> 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;      /Type body created.    SQL> create or replace FUNCTION en_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING en_concat_im ;      /    Function created.    SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)

SYS;SYSTEM;YANGTK;TEST;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSYS;EXFSYS;DBSNMP;WM
SYS;APPQOSSYS;APEX_030200;OWBSYS_AUDIT;ORDDATA;CTXSYS;ANONYMOUS;SYSMAN;XDB;ORDPL
UGINS;OWBSYS;SI_INFORMTN_SCHEMA;OLAPSYS;ORACLE_OCM;XS$NULL;MDDATA;DIP;APEX_PUBLI
C_USER;SPATIAL_CSW_ADMIN_USR;SPATIAL_WFS_ADMIN_USR

以上改变还存在一个潜在的性能风险，那就是使用CLOB时会用到临时段，在高压力系统下，临时文件的大量占用会导致临时表空间的迅速增长。使用10046事件跟踪，可以清晰的看到这些变化，以下是使用10046事件跟踪两个查询：

SQL> alter session set events '10046 trace name context forever,level 12';Session altered.SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)

SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WM

SQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)

查询日志输出如下，其中Disk file operations I/O 操作了文件201，即临时文件：

SQL> alter session set events '10046 trace name context forever,level 12';Session altered.SQL> select wmsys.wm_concat(username) from dba_users;

WMSYS.WM_CONCAT(USERNAME)

SYS,SYSTEM,YANGTK,TEST,OUTLN,MGMT_VIEW,FLOWS_FILES,MDSYS,ORDSYS,EXFSYS,DBSNMP,WMSQL> select en_concat(username) from dba_users;

EN_CONCAT(USERNAME)

阅读全文

1 0