11g 之后wm_contact

ゝ一世哀愁。 2022-08-18 11:54 174阅读 0赞

wm\_contact是undocument的方法，如果你想用,就自己创建。11g之后，建议用listagg

create or replace TYPE zh\_concat\_im

AUTHID CURRENT\_USER AS OBJECT  
(  
  CURR\_STR VARCHAR2(32767),  
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh\_concat\_im) RETURN NUMBER,  
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh\_concat\_im,   
               P1 IN VARCHAR2) RETURN NUMBER,  
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh\_concat\_im,  
                                 RETURNVALUE OUT VARCHAR2,  
                                 FLAGS IN NUMBER)  
                     RETURN NUMBER,  
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh\_concat\_im,   
                    SCTX2 IN  zh\_concat\_im) RETURN NUMBER  
);  
/  
  
  
create or replace TYPE BODY zh\_concat\_im  
IS  
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh\_concat\_im)  
  RETURN NUMBER   
  IS  
  BEGIN  
    SCTX := zh\_concat\_im(NULL) ;  
    RETURN ODCICONST.SUCCESS;  
  END;  
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh\_concat\_im,  
          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 zh\_concat\_im,  
                                 RETURNVALUE OUT VARCHAR2,  
                                 FLAGS IN NUMBER)  
    RETURN NUMBER   
  IS  
  BEGIN  
    RETURNVALUE := CURR\_STR ;  
    RETURN ODCICONST.SUCCESS;  
  END;  
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh\_concat\_im,   
                                   SCTX2 IN zh\_concat\_im)   
  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\_concat1(P1 VARCHAR2)   
RETURN VARCHAR2 AGGREGATE USING zh\_concat\_im ;