oracle wm_concat 函数无法使用的情况下，使用LISTAGG（）函数

http://dacoolbaby.iteye.com/blog/1698957

--20180327

重写wm_concat函数，解决行数超过上限问题

/*执行前请将APPS替换为当前登录用户*/

--1.创建自定义类型t_yd_concat
CREATE OR REPLACE TYPE APPS.t_yd_concat
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR clob,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN t_yd_concat,
RETURNVALUE OUT clob,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
SCTX2 IN t_yd_concat) RETURN NUMBER
);
/

--2.创建自定义type body

 

CREATE OR REPLACE TYPE BODY APPS.t_yd_concat
IS
/*创建静态函数ODCIAGGREGATEINITIALIZE*/
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT t_yd_concat)
RETURN NUMBER
IS
BEGIN
SCTX := t_yd_concat(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
/*创建成员函数ODCIAGGREGATEITERATE*/
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT t_yd_concat,

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;
/*
创建成员函数ODCIAGGREGATETERMINATE

*/
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN t_yd_concat,
RETURNVALUE OUT clob,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
/*
创建成员函数ODCIAGGREGATEMERGE
*/
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT t_yd_concat,
SCTX2 IN t_yd_concat)
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;
/

--3.建立自定义函数yd_concat

CREATE OR REPLACE FUNCTION APPS.yd_concat(P1 VARCHAR2)
RETURN clob AGGREGATE USING t_yd_concat ;

--4.测试

SELECT yd_concat(P.PROJECT_NAME) FROM SPM_CON_PROJECT P;