使用的代碼
代碼如下
-- 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_list my_tk_str_tab_type,
p_separator varchar2 default ',',
p_sort integer default 1
) return varchar2 is
l_idx pls_integer := 0;
l_str varchar2(32767) := null;
l_spt varchar2(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_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_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的表上進行三種方法的測試，運行時間單位秒
數據量PCT Collect SYS_Connect UDF
1% 0.017 0.018 0.017
10% 0.026 0.050 0.029
50% 0.057 2.45 0.065
100% 0.090 5.00 1.06
