《关于oracle字符串汇总.doc》由会员分享，可在线阅读，更多相关《关于oracle字符串汇总.doc（7页珍藏版）》请在金锄头文库上搜索。
1、关于oracle字符串汇总使用的代码代码如下:- sample datadrop table t_collect purge;create table t_collect asselect mod(rownum,30) as flag, lpad(dbms_random.string(l,3),4, ) as valfrom dual connect by rownumcollect函数(Oracle10g)- 1: use collect funtionselect flag,my_tk.f_list2str(cast(collect(trim(val) as my_tk_str_tab_t
2、ype) as enamefrom t_collect sample(10)group by flagorder by 1;sys_connect_by_path- 2: use sys_connect_by_path and row_number functionselect t1.flag, substr(max(sys_connect_by_path(t1.val, ,), 2) qfrom (select a.flag,trim(a.val) as val,row_number over(partition by a.flag order by a.val) rnfrom t_coll
3、ect sample(10) a) t1start with t1.rn = 1connect by t1.flag = prior t1.flagand t1.rn - 1 = prior t1.rngroup by t1.flagorder by 1;user-defined-function- 3: use user-defined-functionselect flag,string_agg(trim(val) as enamefrom t_collect sample(10)group by flagorder by 1;辅助用的my_tk包代码片段代码如下:create or re
4、place 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 isl_idx pls_integer := 0;l_str varchar2(32767) := null;l_spt varchar2(10) := null;l_list my_tk_str_tab_type := p_list;beginif
5、 p_sort = 1 thenl_list := f_sort_list(p_list);end if;l_idx := l_list.first;while l_idx is not null loopl_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-functionCREATE OR REPLACE TYPE t_string_agg AS OBJECT(g
6、_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)R
7、ETURN 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 ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)RETURN NUMBER ISBEGINsctx := t_string_agg(NULL);RETURN ODCIConst.Success;END;MEMBE
8、R FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,value IN VARCHAR2 )RETURN NUMBER ISBEGINSELF.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 ISBEGINreturnVal
9、ue := 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 ISBEGINSELF.g_string := SELF.g_string | , | ctx2.g_string;RETURN ODCIConst.Success;END;END;/CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)RETURN VARCHAR2PARALLEL_ENABLE AGGREGATE USING t_string_agg;/最后给个参考表在数据量10000的表上进行三种方法的测试，运行时间单位秒数据量PCT Collect SYS_Connect UDF1% 0.017 0.018 0.01710% 0.026 0.050 0.02950% 0.057 2.45 0.065100% 0.090 5.00 1.06
