(CURR_STR VARCHAR2(32767),CURR_STR_C CLOB,STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER,MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER);/定义类型body:CREATE OR REPLACE TYPE BODY wm_concat_impl ISSTATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER ISBEGINSCTX := WM_CONCAT_IMPL(NULL,NULL) ;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER ISBEGINIF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THENIF(CURR_STR IS NOT NULL) THENCURR_STR := CURR_STR || ',' || P1;ELSECURR_STR := P1;END IF;ELSEIF (CURR_STR_C IS NULL) THENCURR_STR_C := CURR_STR ;CURR_STR := NULL ;END IF ;CURR_STR_C := CURR_STR_C || ',' || P1;END IF ;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER ISBEGINIF (CURR_STR IS NOT NULL) THENRETURNVALUE := CURR_STR ;ELSERETURNVALUE := CURR_STR_C ;END IF ;RETURN ODCICONST.SUCCESS;END;MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER ISBEGINIF(SCTX2.CURR_STR IS NOT NULL) THENSELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;END IF;RETURN ODCICONST.SUCCESS;END;END;/自定义行变列函数:CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL ;/创建同义词并授权给public:[sql] view plain copycreate public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL/create public synonym wm_concat for wmsys.wm_concat/grant execute on WM_CONCAT_IMPL to public/grant execute on wm_concat to public/
wmsys.wmconcat mysql_感恩节,赠送oracle 12C wmsys.wm_concat()函数
最新推荐文章于 2021-09-01 11:10:47 发布