This article describes in detail the article about oracle strings. If you need it, you can refer to the following.
This article describes in detail the article about oracle strings. If you need it, you can refer to the following.
This article describes in detail the article about oracle strings. If you need it, you can refer to the following.
Code used
| The Code is as follows: |
|
-- Sample data
Drop table t_collect purge;
Create table t_collect
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 |
