Oracle 自定義wmsys.wm_concat 避免長度限制 網上搜索到這段類似代碼但是長度程序還是不行，經過我的修改，親測可以。
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
)
GO
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;
GO
CREATE OR REPLACE FUNCTION ZH_CONCAT(P1 VARCHAR2)
RETURN CLOB AGGREGATE USING ZH_CONCAT_IM ;
uj5u.com熱心網友回復：
so~你的問題是？uj5u.com熱心網友回復：
自己寫自定義函式太麻煩,用xmlagg就可以了,支持回傳clob 和string型別select rtrim(xmlagg(xmlparse(content l||',' wellformed)) .getclobval() ,',') /* .getstringval()*/ from
(select level as l from dual connect by level<=5000)
uj5u.com熱心網友回復：
他沒有問題，估計就是想記錄一下，其實寫個博客更好。
uj5u.com熱心網友回復：
下次博客了，so，沒有問題，呵呵
uj5u.com熱心網友回復：
oracle11g和10g回傳的型別不同uj5u.com熱心網友回復：
成功，但存在編譯錯誤uj5u.com熱心網友回復：
clob 是以性能為代價的，這個從10205和11.2開始就是這個型別了。該函式為oracle內部函式，不建議使用，在12c中已經不提供該函式，關鍵建議listagguj5u.com熱心網友回復：
這種寫法很普遍了，網上都有。自己以前總結過這幾種用法，歡迎大家指正。
假如這種寫法的名字是clob_concat,原寫法回傳varchar2的名字是vc_concat。
listagg限制太多，只有在11g才有且只能回傳varchar2型別。vc_concat和clob_concat這兩個內部函式不能內部排序，且內部聚合是混亂的。比如
select wm_concat(col1) col3,wm_concat(col2) col4 from tab;
回傳的col3和col4里的聚合資料未必是一一對應的，這點不注意很容易出錯。
關于效率對比：
分情況：
1、需要回傳varchar2，不需排序的效率對比：【vc_concat】>【listagg】>【xmlagg】
2、需要回傳clob，不需要排序的效率對比：【clob_concat】>【xmlagg】
3、需要回傳varchar2，且需要排序的效率對比：【listagg】>【xmlagg】>【vc_concat網上流傳的利用分析函式排序后取最大值的方法】
4、需要回傳clob，且需要排序的效率對比：【xmlagg】>【clob_concat網上流傳的利用分析函式排序后取最大值的方法】
總結：
不涉及排序或者對應的話優先選擇vc_concat或者clob_concat，有排序的情況優先選擇listagg，再考慮用2樓xmlagg的方法。
uj5u.com熱心網友回復：
你這是犧牲性能為代價，資料一旦過多要執行好久轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97872.html
標籤：開發
上一篇：求推薦oracle書籍