解决 wm_concat函数长度不够问题

朴灿烈づ我的快乐病毒、 2021-12-23 23:34 506阅读 0赞

1 --创建类型
     2 CREATE OR REPLACE TYPE "T_LINK_LOB" AS OBJECT
     3 (
     4   V_LOB CLOB,
     5   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB)
     6     RETURN NUMBER,
     7   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT NOCOPY T_LINK_LOB,
     8                                        VALUE IN VARCHAR2) RETURN NUMBER,
     9   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN T_LINK_LOB,
    10                                          RETURNVALUE OUT NOCOPY CLOB,
    11                                          FLAGS       IN NUMBER)
    12     RETURN NUMBER,
    13   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB,
    14                                      CTX2 IN T_LINK_LOB) RETURN NUMBER
    15 );
    16 
    17 --创建类型内容
    18 CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
    19   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB)
    20     RETURN NUMBER IS
    21   BEGIN
    22     SCTX := T_LINK_LOB(NULL);
    23     DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
    24     DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
    25     RETURN ODCICONST.SUCCESS;
    26   END;
    27 
    28   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT NOCOPY T_LINK_LOB,
    29                                        VALUE IN VARCHAR2) RETURN NUMBER IS
    30   BEGIN
    31     DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
    32     RETURN ODCICONST.SUCCESS;
    33   END;
    34 
    35   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN T_LINK_LOB,
    36                                          RETURNVALUE OUT NOCOPY CLOB,
    37                                          FLAGS       IN NUMBER) RETURN NUMBER IS
    38   BEGIN
    39     DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
    40     DBMS_LOB.COPY(RETURNVALUE,
    41                   SELF.V_LOB,
    42                   DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
    43     RETURN ODCICONST.SUCCESS;
    44   END;
    45 
    46   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB,
    47                                      CTX2 IN T_LINK_LOB) RETURN NUMBER IS
    48   BEGIN
    49     NULL;
    50     RETURN ODCICONST.SUCCESS;
    51   END;
    52 END;
    53 
    54 --创建函数
    55 CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB AGGREGATE USING T_LINK_LOB;
    56 
    57 --调用方式
    58 select F_LINK_LOB(d.sid) from pmp_p_info d;

转载于:https://www.cnblogs.com/webczw/p/4177638.html