Oracle内测函数之wm_concat随笔

小结:

1、wm_concat函数属于Oracle内测函数,并未对外发布;

2、由于其功能强大,简单易用,深受很多开发喜爱;

3、该函数也存在一定的性能问题,为此,Oracle官方在11g版本正式推出发布了listagg函数,

用以替代wm_concat函数,但功能上无法实现完全替代;

4、自12c版本开始,wm_concat函数正式从软件代码中移除

 

注意:

    1、10g版本wm_concat函数范围值为varchar2,而11g的wm_concat函数返回值为clob类型;

    2、由于clob数据类型存在长时间占用temp空间不释放问题,因此需要格外关注和处理!

 

 

10g


wm_concat 函数定义:

wrap加密定义:
CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
58 89
G2IOCTwNs2RlB9JzCCQPJq/UuH0wg8eZgcfLCNL+Xuf+8L8z/sefdLPnfMPnx3TAM7h0ZSXD
j57Asr2ym9ZtFldFmFu+RdpAU8VGORKSvh2BRIA5Hq8yxhKSOc4WVIKmpu8+Hw0=

 

unwrap解密定义:

CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" (P1 VARCHAR2) 
RETURN VARCHAR2 AGGREGATE USING WMSYS.WM_CONCAT_IMPL ;


wrap加密 WM_CONCAT_IMPL  type定义:

CREATE OR REPLACE TYPE "WMSYS"."WM_CONCAT_IMPL" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
d
270 14c
CeqytwO8EkR6qlYYmJo9223iCgQwg433r0gVfHRAv/9ezrTycSsUV4qRrP16bN5DV364Kfid
RKHz0x7fNrwUJSy1mrtUru+v6nWKL2L82zjScHweN/5wA+fpkMBm+1l8iiL65d50RyWJw4/j
4CgWTAO5YRN5mGXpWRiXb5J2O5svOLS6YMFGdsVSkQHSz+gbk3M50SM86UZvRiEMRnVqSpGf
ssNktb7Foa3sory1BcBElYaA8LuBQ3mpnPcCBFcZhB/dbLu/TPSA0liL28RWrqAzHEzlUPZo
PHqmegAJylJkc+5xMtBMzTA06QuD0MSZwOi3kw==

 

unwrap解密 WM_CONCAT_IMPL  type定义:

CREATE OR REPLACE TYPE "WMSYS"."WM_CONCAT_IMPL"
AUTHID CURRENT_USER 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
);

 

 

wrap加密 WM_CONCAT_IMPL  type body定义:

CREATE OR REPLACE TYPE BODY "WMSYS"."WM_CONCAT_IMPL" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
e
431 1be
XZAuOmsdXEIZmJkPgXcOr20pRmMwgzsJr0gVfHRVWGSl8CvmjY9XoITsRa8J/Cv0HQsfP0bQ
DyVlPH975fR2BhZhmgNPQXbaRsI7SrgR3EUBO12iLhcfXR+mh4nIQHtLbyeIg/K3ZdRrzKzb
FBpWEHCLDTQdlc/I/W56UVeJjwRXIWx74ywAn/RAV9SF8UUPmkh7gss8i0vNDNEv9CSnMD7P
UKvtrpNdJ5jjLa8cquBUNR+Ei7M8/klMgaJqKTwmyWRuGT7E+jjU08MXe1e/mwbgnlOUBG/I
pGVxS2SUUgUe+M9AQydY5sQcYekeCNzGKtdcGhLxfMycQDBW16BWthd1OGR+3YexllXJoDwr
iE9aW0xOCNy3CdA61OYiofc5kDrrFqUwZK93E5rA/4EJmod6n8OrAwQTXcHGxMRIprWGGvWd
z+GTQvks

 

unwrap解密 WM_CONCAT_IMPL  type body定义:

CREATE OR REPLACE TYPE BODY "WMSYS"."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;
/

 

 

 

 

 

========================================================================

 

11g


wm_concat 函数定义:

wrap加密定义:
create or replace function wmsys.wm_concat wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
53 96
antgYqrbNGLSC7Re+71hueZFyT4wg0SvLZ6pyi+mUCJD1KOsoxPiallQXtwu7BTsCmx9/hIg
+ln6MEC75cHHT8YFQPvfjqPM1MuiY1Z0kXN0TQ0W8KE1SkAqjh/+tB/q+oI45dREmV5OHaYy
H/E=

 

unwrap解密定义:

CREATE OR REPLACE FUNCTION "WMSYS"."WM_CONCAT" (P1 VARCHAR2) 
RETURN CLOB AGGREGATE USING WMSYS.WM_CONCAT_IMPL ;


wrap加密 WM_CONCAT_IMPL  type定义:

CREATE OR REPLACE TYPE "WMSYS"."WM_CONCAT_IMPL" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
d
1f8 150
YUIR1Dz0MSRYQuBTM5CNlAO2qhIwg43INQxqZy8CAMHqR383cV9wKEEbBfqfkb+2yRe6zQE+
AL0rAOf5EMTMGONve5yIi6/+MK4sSazQhBmdvIq/uzoppTN0dfWVDHyhRqyLjqGm719zAIg7
/J7JPoCUFgNgf9WJ4rwpb8Rt6HOoF15IeDN5XEHMzDHdyKzMHB1fDGoRO8jpbMvYX0rEw4CM
KN3prp79T9cEdCfJJL45RiGhqcjPrmVeAKRytU3yCSuk+rKq77p14q690YsfNMG4wM7w+VE7
s2zsO8HFcLgRhsSVlUpB20Fyx9hk2g9xPWhMEOF0bAee

 

unwrap解密 WM_CONCAT_IMPL  type定义:

CREATE OR REPLACE TYPE "WMSYS"."WM_CONCAT_IMPL"
AUTHID CURRENT_USER AS OBJECT
(
  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
);

 

 

wrap加密 WM_CONCAT_IMPL  type body定义:

CREATE OR REPLACE TYPE BODY "WMSYS"."WM_CONCAT_IMPL" wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
e
50b 233
DnVAN3VErwu4YGjPqcEiq7dJrt8wgw1eLdxqfC8BuepoPKIYxaP0a445xr4ap3ZJwpYCwqJN
ttNEORSnP3qf02OhEwnF2/FtELsZkE9P3pDvRpOgQGR5vkKqBXqqp5YF0OovyTZw4/ePLx8Y
t4WOkfMfVqEhG19pFjWsoQhGQe7S5xWLmawHoF1YBl/y94slW/DDEUTlWoR3Knnj9Gnfgzni
pTP5DoVeAok3+M5LTbiy9B4gUkAb6I928FKTw7PgkQsYXAe2atMn/XrWZRxBIanPntXoOvEp
MVgt6XQclqDRsinxBVZTODcmEZ2aucmrFOVGp3mXnRPXwXixvCig2+yzUjMMgqyj+wc3OTZQ
4NgnzbVIW4ZwiuYiTCFpiTlbCnsv2mTjHlxbIs9FGUsSYJl93MIEfpMpULuePZCQycIKhl6B
DLV+nh70w008bwc/TTrqWW9UOgLZVnTKp1Z/8JbRS/oIkN/Gu5+tK8LHYkmpK02q80npkOTk
h2iGkEwzGTv6CuX6QgqY/7fi9JL9Esd2L5+VzjtvuceBsuYnNbc=

 

unwrap解密 WM_CONCAT_IMPL  type body定义:

CREATE OR REPLACE TYPE BODY "WMSYS"."WM_CONCAT_IMPL"
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER IS
  BEGIN
    SCTX := WM_CONCAT_IMPL(NULL,NULL) ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER IS
  BEGIN
    IF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THEN
      IF(CURR_STR IS NOT NULL) THEN 
        CURR_STR := CURR_STR || ',' || P1;
      ELSE
        CURR_STR := P1;
      END IF;
    ELSE
      IF (CURR_STR_C IS NULL) THEN
        CURR_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 IS
  BEGIN
    IF (CURR_STR IS NOT NULL) THEN
      RETURNVALUE := CURR_STR ;
    ELSE
      RETURNVALUE := CURR_STR_C ;
    END IF ;
    RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 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;
/

 

posted @ 2022-02-10 08:28  Eddie小陈  阅读(1097)  评论(0编辑  收藏  举报