解决 wm_concat函数长度不够问题

最新推荐文章于 2024-09-08 10:40:56 发布

weixin_33975951

最新推荐文章于 2024-09-08 10:40:56 发布

阅读量275

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/sourcecoding/blog/183270

版权

2019独角兽企业重金招聘Python工程师标准>>>

自定义函数如下：

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;

注：网上找的，我也看不懂

转载于:https://my.oschina.net/sourcecoding/blog/183270