关于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; 

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