| 标题 | 关于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 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。