Oracle多行合并一行的两种函数代码

–方法A:
–创建type头
create or replace type string_sum_obj as object(sum_string varchar2(4000),–聚合函数的实质就是一个对象
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number, --对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number, --聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number, --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number); --终止聚集函数的处理,返回聚集函数处理的结果

–创建type具体
create or replace type body string_sum_obj is static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
begin
v_self := string_sum_obj(null);
return ODCICONST.Success;
end;

member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is
begin
/* 连接，解决逗号分隔第一个字母是逗号的问题 /
if not self.sum_string is null then
self.sum_string := self.sum_string ||’,’|| value;
else
self.sum_string := self.sum_string || value;
end if;
return ODCICONST.Success;
/ 最大值 /
if self.sum_string<value then
self.sum_string:=value;
end if;
/ 最小值 */
if self.sum_string>value then
self.sum_string:=value;
end if;
return ODCICONST.Success;
end;

member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
begin
/* 连接 /
self.sum_string := self.sum_string || v_next.sum_string;
return ODCICONST.Success;
/ 最大值 /
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
/ 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
return ODCICONST.Success;
end;

member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is
begin
return_value:= self.sum_string;
return ODCICONST.Success;
end;
end;

–创建函数
create or replace function wm_concat(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;

–方法B:
–先创建这个类型
create or replace type strcat_type as object(cat_string varchar2(4000),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number);

–类型体内容
create or replace type body strcat_type is static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number is
begin
cs_ctx := strcat_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT strcat_type, value IN varchar2) return number is
begin
self.cat_string := self.cat_string || ‘;’|| value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN Out strcat_type, returnValue OUT varchar2, flags IN number) return number is
begin
returnValue := ltrim(rtrim(self.cat_string,’;’),’;’);
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN Out strcat_type) return number is
begin
self.cat_string := self.cat_string || ‘;’ || ctx2.cat_string;
return ODCIConst.Success;
end;
end;

–创建函数
CREATE OR REPLACE FUNCTION strcat(input varchar2) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;

–取数示例：
select strcat(user_name) from fnd_user where user_id < 100;
select wm_concat(user_name) from fnd_user where user_id < 100;

–函数listagg也可以直接实现功能(可配合Group By使用)
select listagg(fu.user_name, ‘,’) within group(order by fu.user_id) names
from fnd_user fu
where fu.user_id < 100;