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