如何在Oracle中创建自定义聚合函数以模拟wm_concat并返回CLOB数据类型
最编程
2024-01-12 14:46:29
...
因为数据库的wm_concat()函数返回的是VARCHAR2,但是我有个聚合操作,聚合后的参数长度超过4000,所以我需要写一个类似于wm_concat的函数,返回的是CLOB类型
我的报错sql如下:
下面讲解我的操作步骤:
先以sys用户授权:
grant execute on dbms_lob to xxx;
第一步:
第二步:
第三步:
第四步:
第五步:
sql如下:
第一步:
CREATE OR REPLACE TYPE typ_concat_clob AS OBJECT ( concat_str CLOB, splitstr CLOB, --ODCIAggregateInitialize做一些初始化操作 STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return number, --ODCIAggregateIterate是主要的处理逻辑所在,这里定义一个迭代操作 member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue in VARCHAR2) return number, -- ODCIAggregateMerge是一个合并函数,如果在使用时指定了partition enabled,就必须定义此函数,用来把并行处理的结果进行合并 member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return number, --ODCIAggregateTerminate是一个终止函数,顾名思义,在这个函数中对结果做最后处理并返回 member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return number )
第二步:
CREATE OR REPLACE TYPE BODY typ_concat_clob IS STATIC FUNCTION ODCIAggregateInitialize ( cs_ctx IN OUT typ_concat_clob ) return NUMBER IS BEGIN cs_ctx := typ_concat_clob ( to_clob( ',' ), to_clob( ',' )); RETURN ODCICONST.SUCCESS; END; member FUNCTION ODCIAggregateIterate ( self IN OUT typ_concat_clob, srcvalue IN VARCHAR2 ) return NUMBER IS BEGIN dbms_lob.append ( self.concat_str, to_clob( srcvalue )); dbms_lob.append ( self.concat_str, self.splitstr ); RETURN ODCICONST.SUCCESS; END; member FUNCTION ODCIAggregateMerge ( self IN OUT typ_concat_clob, ctx2 IN OUT typ_concat_clob ) return NUMBER IS BEGIN IF ctx2 IS NOT NULL THEN dbms_lob.append ( self.concat_str, ctx2.concat_str ); END IF; RETURN ODCICONST.SUCCESS; END; member FUNCTION ODCIAggregateTerminate ( self IN OUT typ_concat_clob, returnValue OUT CLOB, flags IN NUMBER ) return NUMBER IS BEGIN dbms_lob.trim( self.concat_str, dbms_lob.getlength ( self.concat_str ) - 1 ); self.concat_str := dbms_lob.substr( self.concat_str, dbms_lob.getlength ( self.concat_str ) - 1, 2 ); returnValue := self.concat_str; RETURN ODCICONST.SUCCESS; END; END; /
第三步:
CREATE
OR REPLACE FUNCTION fun_concat_clob ( in_str VARCHAR2 ) RETURN CLOB AGGREGATE USING typ_concat_clob;
/
第四步:
create table test_22 as( select fun_concat_clob('a') as id from dual )
使用和wm_concat一样,不同的是内部处理和返回值都是CLOB类型的。
原文地址:https://www.cnblogs.com/supiaopiao/p/14506384.html