今天开发人员提出了一个需求,如题,具体如何实现,当时想到了oracle自带的函数sys_connect_by_path,但是有个弊端,当连接后的字符串长度大于4000个字节的时候,oracle就无法显示了,会报错ORA-01489,网上查了些资料,这里提供给大家具体的实现方法:
1.创建clob字段类型的type:
create or replace type string_agg_type_clob as object
(
total clob,
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type_clob)
return number,
member function ODCIAggregateIterate(self IN OUT string_agg_type_clob,
value IN clob) return number,
member function ODCIAggregateTerminate(self IN string_agg_type_clob,
returnValue OUT clob,
flags IN number)
return number,
member function ODCIAggregateMerge(self IN OUT string_agg_type_clob,
ctx2 IN string_agg_type_clob)
return number
);
/
2.相应的类体:
create or replace type body string_agg_type_clob is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type_clob)
return number is
begin
sctx := string_agg_type_clob(null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type_clob,
value IN clob) return number is
begin
self.total := self.total || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type_clob,
returnValue OUT clob,
flags IN number) return number is
begin
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type_clob,
ctx2 IN string_agg_type_clob)
return number is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/
3.实现转换功能的函数:
CREATE or replace FUNCTION straggc(input clob) RETURN clob
PARALLEL_ENABLE
AGGREGATE USING string_agg_type_clob;
/
具体示例如下,其中是以'|'符号作分隔符的,当然也可以用其他的符号代替。
转换前:
转换后:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-716678/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-716678/