WM_CONCAT函数

创建包，包体，函数

CREATE
OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT (
CURR_STR VARCHAR2 ( 32767 ),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE ( SCTX IN OUT WM_CONCAT_IMPL ) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE ( SELF IN OUT WM_CONCAT_IMPL, P1 IN VARCHAR2 ) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE ( SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER ) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE ( SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL ) RETURN NUMBER
);

定义类型body：

CREATE
OR REPLACE TYPE BODY WM_CONCAT_IMPL IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE ( SCTX IN OUT WM_CONCAT_IMPL ) RETURN NUMBER IS BEGIN
SCTX := WM_CONCAT_IMPL ( NULL );
RETURN ODCICONST.SUCCESS;

END;
MEMBER FUNCTION ODCIAGGREGATEITERATE ( SELF IN OUT WM_CONCAT_IMPL, 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 WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER ) RETURN NUMBER IS BEGIN
RETURNVALUE := CURR_STR;
RETURN ODCICONST.SUCCESS;

END;
MEMBER FUNCTION ODCIAGGREGATEMERGE ( SELF IN OUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL ) 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_concat ( P1 VARCHAR2 ) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL;

 

创建同义词并授权(可忽略)

create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL
create public synonym wm_concat for sys.wm_concat
grant execute on WM_CONCAT_IMPL to public
grant execute on wm_concat to public