該函式可以實現行列轉換：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

WM_CONCA函式兩行合成一行資料

相關文章