创建聚合函数:
1.创建TYPE
2.创建TYPE BODY
3.定义FUNCTION
CREATE OR REPLACE TYPE T_LINK_LOB AS OBJECT
(
V_LOB CLOB,
--初始化函数
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB)
RETURN NUMBER,
--迭代
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB,
VALUE IN VARCHAR2) RETURN NUMBER,
--终止并还回值
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB,
RETURNVALUE OUT NOCOPY CLOB,
FLAGS IN NUMBER)
RETURN NUMBER,
--合并
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB,
CTX2 IN T_LINK_LOB) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB)
RETURN NUMBER IS
BEGIN
SCTX := T_LINK_LOB(NULL);
DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB,
RETURNVALUE OUT NOCOPY CLOB,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
DBMS_LOB.COPY(RETURNVALUE,
SELF.V_LOB,
DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB,
CTX2 IN T_LINK_LOB) RETURN NUMBER IS
BEGIN
NULL;
RETURN ODCICONST.SUCCESS;
END;
END;
CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB
AGGREGATE USING T_LINK_LOB;