Oracle 拼接字符串

刁5826

.CREATE OR REPLACE FUNCTION stragg(input varchar2) RETURN varchar2
PARALLEL_ENABLE
AGGREGATE USING string_agg_type;
--1

CREATE OR REPLACE TYPE "STRING_AGG_TYPE" as object
(
total varchar2(4000),
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number,
member function ODCIAggregateIterate(self  IN OUT string_agg_type,
value IN varchar2) return number,
member function ODCIAggregateTerminate(self        IN string_agg_type,
returnValue OUT varchar2,
flags       IN number)
return number,
member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type) return number
)
--2
create or replace type body string_agg_type is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number is
begin
sctx := string_agg_type(null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self  IN OUT string_agg_type,
value IN varchar2) return number is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self        IN string_agg_type,
returnValue OUT varchar2,
flags       IN number) return number is
begin
returnValue := ltrim(self.total, ',');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type) return number is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;

　　
　　依次创建上面三个方法，使用方法
　　select stragg(distinct t.id) id from tdgl_sinformation t;
　　
　　
　　这个代码是将数据库同一个字段的不同行的值进行拼接。用逗号隔开