关于oracle字符串汇总

本文章详细的讲述了关于oracle字符串的文章，有需要的朋友可以参考一下下哈。

本文章详细的讲述了关于oracle字符串的文章，有需要的朋友可以参考一下下哈。

使用的代码

代码如下

复制代码

-- sample data drop table t_collect purge; create table t_collect as   mod(rownum,30) as flag, lpad(dbms_random.string('l',3),4,' ') as val   from dual connect by rownum 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