加强型WM_CONCAT2函数（不受32767限制的CLOB类型）

CREATE OR REPLACE TYPE WM_CONCAT_IMPL2 AS OBJECT

-- AUTHID CURRENT_USER AS OBJECT

(

CURR_STR nclob, -- 原来为varchar2(32767)

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL2,

P1 IN nclob) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL2,

RETURNVALUE OUT nclob,

FLAGS IN NUMBER)

RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL2,

SCTX2 IN WM_CONCAT_IMPL2) RETURN NUMBER

)

;

--定义类型body:

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL2

IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL2)

RETURN NUMBER

IS

BEGIN

SCTX := WM_CONCAT_IMPL2(NULL) ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL2,

P1 IN nclob)

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 WM_CONCAT_IMPL2,

RETURNVALUE OUT nclob,

FLAGS IN NUMBER)

RETURN NUMBER

IS

BEGIN

RETURNVALUE := CURR_STR ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL2,

SCTX2 IN WM_CONCAT_IMPL2)

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 wm_concat2(P1 nclob)

RETURN nclob AGGREGATE USING WM_CONCAT_IMPL2 ;

/

create or replace procedure a_test(mycol in varchar2,

mytab in varchar2,

mywhere in varchar2,

mysql out varchar2,

my_cursor2 out sys_refcursor) as

my_cursor sys_refcursor;

mysql99 clob;

begin

mysql := 'select (''select ''''''||replace(wm_concat2(to_char(' || mycol ||

')),'','','''''','''''')||'''''' from dual'') from ' || mytab||' where 1=1 '||mywhere;

OPEN my_cursor FOR mysql;

FETCH my_cursor into mysql99;

OPEN my_cursor2 FOR mysql99;

end;

/

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/345618/viewspace-1060944/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/345618/viewspace-1060944/