wm_contact支持的oracle,在11.2.0.3中使用自定义聚合函数EN_CONCAT与EN_CONCAT_DISTINCT替换WMSYS.WM_CONTACT...

有一个项目使用的是Oracle 11.2.0.3 版本，有一条sql使用了WMSYS.WM_CONTACT，发现执行报错，最终发现原因是由于在10.2.0.5与11.2.0.3WMSYS.版本中出现的bug，现使用EN_CONCAT、EN_CONCAT_DISTINCT，代替WM_CONTACT功能，这两个函数由某位大师编写，但是有一些小问题，经过在实践中改进，调整了EN_CONCAT_DISTINCT函数。同事在使用过程中发现EN_CONCAT这个也有点问题，暂时先不改进，先发上改进后的函数，EN_CONCAT后续改进。

代码如下：

----------------------------------------------------

-- Export file for user SCOTT                 --

-- Created by Administrator on 2013-7-4, 18:48:48 --

----------------------------------------------------

spool en_concat.log

prompt

prompt Creating type EN_CONCAT_DISTINCT

prompt ================================

prompt

CREATE OR REPLACE TYPE EN_CONCAT_DISTINCT AUTHID CURRENT_USER AS OBJECT

(

CURR_STR VARCHAR2(32767),

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_DISTINCT)

RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE

(

SELF IN OUT EN_CONCAT_DISTINCT,

P1   IN VARCHAR2

) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE

(

SELF        IN EN_CONCAT_DISTINCT,

RETURNVALUE OUT VARCHAR2,

FLAGS       IN NUMBER

) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE

(

SELF  IN OUT EN_CONCAT_DISTINCT,

SCTX2 IN EN_CONCAT_DISTINCT

) RETURN NUMBER

)

;

/

prompt

prompt Creating type EN_CONCAT_IM

prompt ==========================

prompt

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

)

/

prompt

prompt Creating function EN_CONCAT

prompt ===========================

prompt

CREATE OR REPLACE FUNCTION EN_CONCAT(P1 VARCHAR2) RETURN VARCHAR2

AGGREGATE USING EN_CONCAT_IM;

/

prompt

prompt Creating function EN_CONCAT_DISTINCT

prompt ============================

prompt

CREATE OR REPLACE FUNCTION EN_CONCAT_DISTINCT(P1 VARCHAR2) RETURN VARCHAR2

AGGREGATE USING EN_CONCAT_DISTINCT;

/

prompt

prompt Creating type body EN_CONCAT_DISTINCT

prompt =====================================

prompt

CREATE OR REPLACE TYPE BODY EN_CONCAT_DISTINCT IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT EN_CONCAT_DISTINCT)

RETURN NUMBER IS

BEGIN

-- object initialization

SCTX := EN_CONCAT_DISTINCT(NULL);

RETURN ODCICONST.SUCCESS;

END;

--this function is main function for all logic ,essence is for ..loop

MEMBER FUNCTION ODCIAGGREGATEITERATE

(

SELF IN OUT EN_CONCAT_DISTINCT,

P1   IN VARCHAR2

) RETURN NUMBER IS

BEGIN

IF (CURR_STR IS NOT NULL) AND INSTR(SELF.CURR_STR, P1) = 0 THEN

SELF.CURR_STR := CURR_STR || ',' || TO_CHAR(P1);

END IF;

IF CURR_STR IS NULL THEN

SELF.CURR_STR := TO_CHAR(P1);

END IF;

--remove  repeat value

IF (CURR_STR IS NOT NULL) AND INSTR(SELF.CURR_STR, P1) = 1 THEN

SELF.CURR_STR := SELF.CURR_STR;

END IF;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE

(

SELF  IN OUT EN_CONCAT_DISTINCT,

SCTX2 IN EN_CONCAT_DISTINCT

) RETURN NUMBER IS

BEGIN

IF (SCTX2.CURR_STR IS NOT NULL) AND

INSTR(SELF.CURR_STR, SCTX2.CURR_STR) = 0 THEN

SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;

END IF;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE

(

SELF        IN EN_CONCAT_DISTINCT,

RETURNVALUE OUT VARCHAR2,

FLAGS       IN NUMBER

) RETURN NUMBER IS

BEGIN

RETURNVALUE := SELF.CURR_STR;

RETURN ODCICONST.SUCCESS;

END;

END;

/

prompt

prompt Creating type body EN_CONCAT_IM

prompt ===============================

prompt

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;

/

spool off

注：如果遇到中文有乱码的情况，可使用to_char 例如：select en_concat(to_char(name) from T_USER;