这篇博客介绍了如何通过创建自定义Oracle类型和函数T_LINK_LOB来替代wm_concat，解决当wm_concat函数处理的数据过长时遇到的长度限制问题。函数包括初始化、迭代、终止和合并等步骤，使得能够处理更长的字符串连接。
自定义函数如下：
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;
注：网上找的，我也看不懂
扫一扫
3821
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
