解决oracle数据库wm_concat()字符串拼接过长问题

男娘i 2022-07-16 03:41 1727阅读 0赞

[wm\_concat()][wm_concat]拼接字符串的结果只能达到4000，超过的时候需要重写一个方法，不过实际运行的时候会特别慢，这种函数只能在平时对数据进行单次查询的时候用到，在实际业务中感觉性能太差了，之前读取的数据最后分类为159组，最大组数据为3000不到个元素，总共查询时间大概有480s。

直接上sql了，事实证明是好用的，如果你的navicat不好用的话，试用下sqldeveloper试下。

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 CLOB) 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 CLOB)  
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 CLOB)

RETURN CLOB AGGREGATE USING zh\_concat\_im ;

附上当时执行的sql(感觉没什么用)，业务上的字段我给屏蔽掉了。

SELECT A.C\_ET\_ID,B.C\_N,COUNT(DISTINCT A.C\_EN) AS NUM,zh\_concat(DISTINCT A.C\_EN)  
FROM DU.T\_S\_E A  
LEFT JOIN DU.T\_ET B ON A.C\_ET\_ID = B.C\_ID  
GROUP BY A.C\_ET\_ID,B.C\_N  
ORDER BY NUM DESC;

[wm_concat]: http://blog.csdn.net/wushanyun1989/article/details/11247085