CREATEOR REPLACE TYPE BODY WM_CONCAT_IMPL IS STATICFUNCTION ODCIAGGREGATEINITIALIZE(SCTX INOUT WM_CONCAT_IMPL) RETURN NUMBER IS BEGIN SCTX := WM_CONCAT_IMPL(NULL) ; RETURN ODCICONST.SUCCESS; END; MEMBERFUNCTION ODCIAGGREGATEITERATE(SELF INOUT WM_CONCAT_IMPL, P1 IN VARCHAR2) RETURN NUMBER IS BEGIN IF(CURR_STR ISNOTNULL) THEN CURR_STR := CURR_STR ||','|| P1; ELSE CURR_STR := P1; END IF; RETURN ODCICONST.SUCCESS; END; MEMBERFUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := CURR_STR ; RETURN ODCICONST.SUCCESS; END; MEMBERFUNCTION ODCIAGGREGATEMERGE(SELF INOUT WM_CONCAT_IMPL, SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER IS BEGIN IF(SCTX2.CURR_STR ISNOTNULL) THEN SELF.CURR_STR := SELF.CURR_STR ||','|| SCTX2.CURR_STR ; END IF; RETURN ODCICONST.SUCCESS; END; END; /
自定义行变列函数:
1 2 3
CREATEOR REPLACE FUNCTION wm_concat(P1 VARCHAR2) RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ; /
三.创建同义词并授权
1 2 3 4 5 6 7 8
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL / create public synonym wm_concat for wmsys.wm_concat / grantexecuteon WM_CONCAT_IMPL to public / grantexecuteon wm_concat to public /