關於oracle字串匯總

使用的代碼

代碼如下

複製代碼

-- 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