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

wmsys.wm_concat对象实现行列转换的方法,在11.2.0.3及10.2.0.5有所变化。原本WM_CONCAT函数返回值为VARCHAR2变更为CLOB。这一变化导致了很多程序的异常

11g：

SQL> desc wmsys.wm_concat
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) CLOB
P1 VARCHAR2 IN

WM_CONCAT 11g 返回clob字段，但是关联其他表varchar2字段会报错，所以需要将WM_CONCAT函数返回来的值输出varchar2 才可以和其他表关联。

解决办法如下：

如下一个系列的函数，可以帮助用户构建自有的行列转换函数：（自己可以选择返回值 clob 、varchar2）

创建type：
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((result)=>username) username from dba_users;

username
——————————————————————————————
SYS;SYSTEM;SCOTT;REPORT;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSY

select en_concat(P1 => username) from dba_users;

username
——————————————————————————————
SYS;SYSTEM;SCOTT;REPORT;OUTLN;MGMT_VIEW;FLOWS_FILES;MDSYS;ORDSY

ITPub博客

WM_CONCA函数两行合成一行数据

最新文章