A complicated Stored Procedure for splitting strings multiple times

The special split functions you have written are as follows:
Create or replace function FN_SPLIT_STR_2 (var_str in varchar2) return varchar2
/*
There is a table t1 with a field named c3, which stores the location information of all stores.
Now we need a stored procedure to reduce the coordinate values of the c3 fields of all records by three times and write them to the c field.


Example: 220.25 257,220.25 269.75, 229.25 269.75, 229.25 257
Each comma is separated by coordinate points. Each coordinate point is separated by a space.


Storage function name: FN_SPLIT_STR_2
Purpose: reduce the bi_store coordinate v_coords3 by three times to update coords, for example
Author: huangshan
*/
As
Var_tmp varchar2 (4000 );
Var_element varchar2 (4000 );
Var_result varchar2 (4000 );
Var_1__first number;
Var_instr_second number;
Var_length number;


Begin
Var_tmp: = var_str;
Var_1__first: = 0;
Var_instr_second: = 0;
Var_result: = '';
Var_length: = 0;

/* Replace the passed special characters
Chr (9) tab
Chr (10) Press ENTER
Chr (13) line feed
*/
Var_tmp: = replace (var_tmp, chr (10 ),'');
Var_tmp: = replace (var_tmp, chr (13 ),'');
Var_tmp: = replace (var_tmp, chr (9 ),'');

While instr (var_tmp, '')> 0
Or instr (var_tmp, ',')> 0
Or (var_length> 0) loop
Var_instr_first: = instr (var_tmp ,'');
Var_instr_second: = instr (var_tmp ,',');
-- Dbms_output.put_line ('var _ 0000_kg: '| var_0000_first | '');
-- Dbms_output.put_line ('var _ cmd_dh: '| var_cmd_second | '');
Var_length: = length (var_tmp );
-- Dbms_output.put_line ('var _ length: '| var_length | '');

/* 1 if there is a space first, such as 12 32, 12 32 **/
If var_1__first <var_1__second then
Var_element: = round (to_number (substr (var_tmp, 1, var_instr_first-1 );
Var_result: = var_result | var_element | '';
Var_tmp: = substr (var_tmp, var_1__first + 1, length (var_tmp ));


-- Dbms_output.put_line ('var _ result kg: '| var_result );
-- Dbms_output.put_line ('var _ tmp kg: '| var_tmp | '');
-- Dbms_output.put_line ('var _ element kg: '| var_element | '');
 
/* 2 If the space has been intercepted, the comma is in the front, such as 32, 12 32 **/
Elsif var_instr_first> var_instr_second and var_instr_second> 0 then
Var_element: = round (to_number (substr (var_tmp, 1, var_instr_second-1 );
Var_result: = var_result | var_element | ',';
Var_tmp: = substr (var_tmp, var_1__second + 1, length (var_tmp ));
-- Dbms_output.put_line ('var _ result dh: '| var_result );
-- Dbms_output.put_line ('var _ tmp dh: '| var_tmp | '');
-- Dbms_output.put_line ('var _ element dh: '| var_element | '');

/* 3 if the comma has been intercepted, only the last coordinate x y is left, such as 12 32 **/
Elsif var_1__first> var_1__second and var_1__second = 0 then
Var_element: = round (to_number (substr (var_tmp, 1, var_instr_first-1 );
Var_result: = var_result | var_element | '';
Var_tmp: = substr (var_tmp, + 1, length (var_tmp ));


-- Dbms_output.put_line ('var _ result kg: '| var_result );
-- Dbms_output.put_line ('var _ tmpvar_1__first kg: '| var_tmp | '');
-- Dbms_output.put_line ('var _ element kg: '| var_element | '');

/* 4 if the coordinate has been captured to the last coordinate, such as 32 **/
Elsif var_1__first = 0 and var_1__second = 0 and var_length> 0 then
-- Dbms_output.put_line ('var _ tmp the last one: '| var_tmp | '');
Var_element: = round (to_number (var_tmp)/3, 2 );
Var_result: = var_result | var_element;
Var_tmp: = '';
-- Dbms_output.put_line ('var _ result 0: '| var_result );
-- Dbms_output.put_line ('var _ tmp 0: '| var_tmp | '');
-- Dbms_output.put_line ('var _ element 0: '| var_element | '');

 
/* 5 if there are other things, set it to ''until the while loop is exited **/
Else
Var_tmp: = '';
End if;


-- Dbms_output.put_line ('');

End loop;
Return var_result;


End FN_SPLIT_STR_2;


-- The split function of other google users is as follows:
-- Sharding Function
Create or replace function split_str (var_str in varchar2,
Var_split in varchar2)
/*************************************** *************
Note: first execute the following statement to create a type
Create or replace type t_ret_table is table of varchar2 (100)
** Function name: split_str
** Parameter: [name] [type] [description]
** Var_str varchar2: string to be split
** Var_split varchar2 string Separator
** Return value: Result t_ret_table: array set after split
** Abstract: Splits a string.
Call example:
Select * from table (split_str ('2017-10-21 ','-'))
**************************************** ************/
Return t_ret_table is
Var_out t_ret_table;
Var_tmp varchar2 (4000 );
Var_element varchar2 (4000 );


Begin
Var_tmp: = var_str;
Var_out: = t_ret_table ();
-- If a matched delimiter exists
While instr (var_tmp, var_split)> 0 loop
Var_element: = substr (var_tmp, 1, instr (var_tmp, var_split)-1 );
Var_tmp: = substr (var_tmp,
Instr (var_tmp, var_split) + length (var_split ),
Length (var_tmp ));
-- Var_out.extend (1 );
Var_out.extend;
Var_out (var_out.count): = var_element;
End loop;


-- Var_out.extend (1 );
Var_out.extend;
Var_out (var_out.count): = var_tmp;


Return var_out;
End split_str;