About Oracle String totals

Code to use

The code is as follows

Copy Code

--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 function (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 (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;

Auxiliary MY_TK Package Code fragment

The code is as follows

Copy Code

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 (a): = 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 are not null loops 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

Custom aggregate functions

------------------------------------------------------------------

The code is as follows

Copy Code

--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 are   BEGIN     sctx: = T_string_agg (NULL);     return Odcico Nst. Success;   End;   Member FUNCTION odciaggregateiterate (self   in out  T_string_agg,                                          value   in      VARCHAR2)     return number is   BEGIN  & nbsp;  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; /

Finally give a reference table

test for three methods on a table with data volume 10000, run time per second

Data volume 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