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 NUMBER15 );16 17 --创建类型内容18 CREATE OR REPLACE TYPE BODY T_LINK_LOB IS19   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB)20     RETURN NUMBER IS21   BEGIN22     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 IS30   BEGIN31     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 IS38   BEGIN39     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 IS48   BEGIN49     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;