Oracle string Summary

Code used

The Code is as follows:
-- Sample data
Drop table t_collect purge;
Create table t_collect
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 (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_trim (trim (val) as ename
From t_collect sample (10)
Group by flag
Order by 1;
 

Auxiliary my_tk package code snippet

 

The Code is as follows:
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
Rochelle idx pls_integer: = 0;
L_str varchar2 (32767): = null;
Rochelle SPT varchar2 (10): = null;
Rochelle list my_tk_str_tab_type: = p_list;
Begin
If p_sort = 1 then
Rochelle list: = f_sort_list (p_list );
End if;

Rochelle idx: = maid;
While l_idx is not null loop
L_str: = l_str | l_spt | l_list (l_idx );
Rochelle SPT: = p_separator;
Rochelle idx: = maid (Rochelle idx );
End loop;

Return l_str;
End;
 

Custom Aggregate functions

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

The Code is as follows:

-- User-defined-function
Create or replace type t_string_same AS OBJECT
(
G_string VARCHAR2 (32767 ),

Static function odciaggresponinitialize (sctx in out t_string_timeout)
Return number,

Member function ODCIAggregateIterate (self in out t_string_records,
Value IN VARCHAR2)
Return number,

Member function odciaggresponterminate (self IN t_string_timeout,
ReturnValue OUT VARCHAR2,
Flags in number)
Return number,

Member function ODCIAggregateMerge (self in out t_string_records,
Ctx2 IN t_string_ing)
RETURN NUMBER
);
/
Create or replace type body t_string_region IS
Static function odciaggresponinitialize (sctx in out t_string_timeout)
RETURN NUMBER IS
BEGIN
Sctx: = t_string_agg (NULL );
RETURN ODCIConst. Success;
END;

Member function ODCIAggregateIterate (self in out t_string_records,
Value IN VARCHAR2)
RETURN NUMBER IS
BEGIN
SELF. g_string: = self. g_string | ',' | value;
RETURN ODCIConst. Success;
END;

Member function odciaggresponterminate (self IN t_string_timeout,
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_records,
Ctx2 IN t_string_ing)
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_timeout;
/
 

Finally, a reference table is provided.

Perform a test in three methods on a table with a data volume of 10000, in seconds.

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