Oracle进行字符串汇总

对字符进行汇总（把相关的字符连接成一个串，以符号分隔），有三种常用方法(10g中增加了内置的WMSYS.WM_CONCAT()函数)。在不同数据量下进行测试，运行时间如下表：

在数据量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

使用的代码

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