Detailed description of string segmentation into a set in El

First, two functions are constructed in two ways.

The code is as follows:

Copy code

-- Use conventional plsql Create or replace function f_str2list_pls ( P_str varchar2, P_separator varchar2 default ',' ) Return my_tk_str_tab_type is Rochelle IDX pls_integer: = 0; L_str varchar2 (32767): = trim (p_str ); Rochelle MT varchar2 (100): = null; Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); Begin While l_str is not null loop Rochelle IDX: = instr (l_str, p_separator ); If l_idx = 0 then L_elmt: = l_str; Rochelle str: = null; Else L_elmt: = substr (l_str, 1, l_idx-1 ); L_str: = substr (l_str, l_idx + 1 ); End if;   Rochelle list.extend; L_list (l_list.last): = trim (l_elmt ); End loop; Return l_list; End; / -- Use single SQL Create or replace function f_str2list_ SQL ( P_str varchar2, P_separator varchar2 default ',' ) Return my_tk_str_tab_type is Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); Begin Select substr (a. str, Instr (p_separator | a. str, p_separator, 1, rn ), Instr (a. str | p_separator, p_separator, 1, rn )- Instr (p_separator | a. str, p_separator, 1, rn) q Bulk collect into l_list From (select p_str as str from dual), (Select rownum rn from dual connect by rownum <= length (p_str) B Where instr (p_separator | a. str, p_separator, 1, rn)> 0; Return l_list; End; /

 

 

Verify that the two methods perform the same function

 

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

The code is as follows:

Copy code

-- Same result Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (1000): = 'a, B, c '; Begin Rochelle list: = f_str2list_pls (l_str ,','); For I in 1 .. l_list.count loop Dbms_output.put_line (l_list (I )); End loop;   Dbms_output.put_line (''); Rochelle list: = f_str2list_ SQL (l_str ,','); For I in 1 .. l_list.count loop Dbms_output.put_line (l_list (I )); End loop; End; / SQL> set serveroutput on A B C   A B C

 

 

 

We know that in PL/SQL and SQL, the varchar2 type length limit is different. Are the two methods subject to the same restrictions? Verify

First test the PL/SQL version

 

-- A single letter is used as an element and a comma delimiter is required. The maximum length of an element is 32767 in 2. PL/SQL. When (32767/2 = 16383) elements are retrieved, the operation is successful.
----------------------------------------------------

The code is as follows:	Copy code
-- Pls versions tring length limit Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 16383; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; Rochelle str: = substr (l_str, 2 ); Rochelle list: = f_str2list_pls (l_str ,','); End; / PL/SQL procedure successfully completed

-- Add an element. When getting (16384) elements, it exceeds the limit of varchar2, so it cannot run. (An error is returned when the string is constructed. f_str2list_pls has not been called yet)

The code is as follows:

Copy code

Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 16384; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; Rochelle list: = f_str2list_pls (l_str ,','); End; / ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 9 -- Modify the code and actually call this function. The result shows that the f_str2list_pls () internal error is too long. Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 16383; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; L_list: = f_str2list_pls (l_str | ', ',','); End;   ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "T2.F _ STR2LIST_PLS", line 7 ORA-06512: at line 12

 

 

 

The SQL version is tested below

 

The code is as follows:

Copy code

-- Because the upper limit of varchar2 in SQL is 4000, 2000 elements are used. Everything works. ---------------------------------------------------- -- SQL versions tring length limit Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 2000; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; Rochelle str: = substr (l_str, 2 ); Rochelle list: = f_str2list_ SQL (l_str ,','); End; / PL/SQL procedure successfully completed

-- Directly add to 2001 elements. An error is reported in f_str2list_ SQL (), indicating that the string is successfully input, but cannot be processed by the function.

The code is as follows:

Copy code

Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 2001; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; Rochelle str: = substr (l_str, 2 ); Rochelle list: = f_str2list_ SQL (l_str ,','); End;   ORA-01460: unimplemented or unreasonable conversion requested ORA-06512: at "T2.F _ STR2LIST_ SQL", line 8 ORA-06512: at line 13

Note: the description of the ORA-01460 is not clear, in fact this is varchar2 super long

 

 

 

 

 

Test and compare the performance of the two methods under different data volumes

 

The code is as follows:

Copy code

---------------------------------------------------- -- Performance test Declare Rochelle list my_tk_str_tab_type: = my_tk_str_tab_type (); L_str varchar2 (32767): = ''; Rochelle Max pls_integer: = 2000; Begin -- Construct string For I in 1 .. l_max loop L_str: = l_str | ',' | 'A '; End loop; Rochelle str: = substr (l_str, 2 ); -- Warm up before actually calculation Rochelle list: = f_str2list_ SQL (l_str ,','); Rochelle list: = f_str2list_pls (l_str ,',');   -- Begin calc and diff My_rs.rs_start; -- 1. pls version Rochelle list: = f_str2list_pls (l_str ,','); My_rs.rs_middle; -- 2. SQL version Rochelle list: = f_str2list_ SQL (l_str ,','); My_rs.rs_stop (); End; /

 

We will test the number of elements 100,200,500,100 0, 2000 respectively. Perform 3-5 times in each case and then take the average value

Number of elements

PL/SQL running time (1/100 second)

SQL running time (1/100 second)

PCT

100

1

1

100%

200

1

1-2

50%-100%

500

1-2

2-3

30%-50%

1000

1

4

25%

2000

2

10

20%

Summary:

The SQL version is easy to write and can be used directly from the PL/SQL environment (single SQL for row and column conversion ).

The SQL version can only process strings with a length less than 4000, and can contain a maximum of 2000 elements. PL/SQL can process strings with a length less than 32767.

When the data volume is small, the performance of the two is equivalent. As the data volume increases, PL/SQL performance is superior.