使用的代码
代码如下
-- sample data
drop table t_collect purge;
create table t_collect as
select mod(rownum,30) as flag, lpad(dbms_random.string('l',3),4,' ') as val
from dual connect by rownum<=10000;
collect函数(Oracle10g)
-- 1: use collect funtion
select flag,
my_tk.f_list2str(cast(collect(trim(val)) as my_tk_str_tab_type)) as ename
from t_collect sample(10)
group by flag
order by 1;
sys_connect_by_path
-- 2: use sys_connect_by_path and row_number function
select t1.flag, substr(max(sys_connect_by_path(t1.val, ',')), 2) q
from (select a.flag,
trim(a.val) as val,
row_number() over(partition by a.flag order by a.val) rn
from t_collect sample(10) a) t1
start with t1.rn = 1
connect by t1.flag = prior t1.flag
and t1.rn - 1 = prior t1.rn
group by t1.flag
order by 1;
user-defined-function
-- 3: use user-defined-function
select flag,
string_agg(trim(val)) as ename
from t_collect sample(10)
group by flag
order by 1;
辅助用的my_tk包代码片段
代码如下
create or replace type my_tk_str_tab_type is table of varchar2(100);
---------------------------------------------------------------------
function f_list2str
(
p_listmy_tk_str_tab_type,
p_separator varchar2 default ',',
p_sortinteger default 1
) return varchar2 is
l_idxpls_integer := 0;
l_strvarchar2(32767) := null;
l_sptvarchar2(10) := null;
l_list my_tk_str_tab_type := p_list;
begin
if p_sort = 1 then
l_list := f_sort_list(p_list);
end if;
l_idx := l_list.first;
while l_idx is not null loop
l_str := l_str || l_spt || l_list(l_idx);
l_spt := p_separator;
l_idx := l_list.next(l_idx);
end loop;
return l_str;
end;
自定义聚合函数
------------------------------------------------------------------
代码如下
-- user-defined-function
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
g_stringVARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctxIN OUTt_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(selfIN OUTt_string_agg,
valueINVARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(selfINt_string_agg,
returnValueOUTVARCHAR2,
flagsINNUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(selfIN OUTt_string_agg,
ctx2INt_string_agg)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctxIN OUTt_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(selfIN OUTt_string_agg,
valueINVARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(selfINt_string_agg,
returnValueOUTVARCHAR2,
flagsINNUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(selfIN OUTt_string_agg,
ctx2INt_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
最后给个参考表
在数据量10000的表上进行三种方法的测试，运行时间单位秒
数据量PCTCollectSYS_ConnectUDF
1%0.0170.0180.017
10%0.0260.0500.029
50%0.0572.450.065
100%0.0905.001.06
