Oracle 自定义聚合函数

创建聚合函数：

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;