Oracle string to array Functions

Create type char_table is table of varchar2 (4000); -- create a custom type script

Create or replace function split_string (pi_str in varchar2, pi_separator in varchar2) -- create a function
Return char_table
Is
V_char_table char_table;
-- Create type char_table is table of varchar2 (4000); -- create a custom type script
V_temp varchar2 (4000 );
V_element varchar2 (4000 );
Begin
V_char_table: = char_table ();
V_temp: = pi_str;
While instr (v_temp, pi_separator)> 0
Loop
V_element: = substr (v_temp, 1, instr (v_temp, pi_separator)-1 );
V_temp: = substr (v_temp, instr (v_temp, pi_separator) + length (pi_separator), length (v_temp ));
V_char_table.extend;
V_char_table (v_char_table.count): = v_element;

End loop;
V_char_table.extend;
V_char_table (v_char_table.count): = v_temp;
Return v_char_table;
End split_string;

-- Test

Select split_string ('safs, utjtje, 2412q1a, egwsff ',', ') from dual;

-- Practical application

Create or replace function initCFJG
Return varchar2
Is
Pragma AUTONOMOUS_TRANSACTION;
Guid varchar2 (80): = ''; -- GUID
Zjid varchar2 (80): = ''; -- primary key id
Lxid varchar2 (80): = ''; -- processing result type id
LxidsArr char_table; -- string array. char_table is of the custom type.
Begin
For p in (select lp_id, zfgl_id, case_id, cf_amount from dzjc_xzzf_t_zfgl where lp_id is not null)
Loop
Zjid: = p. zfgl_id;
Select split_string (p. lp_id, ',') into lxidsArr from dual;
For I in 1 .. lxidsArr. count
Loop
-- Dbms_output.put_line (lxidsArr (I ));
Select GETGUID into guid from dual;

Select cfjd_cflx into lxid from cf_t_cfjd where rownum = 1 and cfjd_id = lxidsArr (I );
If lxid = '3' then
Insert into dzjc_xzzf_t_zfgl (zfgl_id, case_id, lp_id, cf_amount) values (guid, p. case_id, '2017 _ '| lxid, p. cf_amount );
Else
Insert into dzjc_xzzf_t_zfgl (zfgl_id, case_id, lp_id) values (guid, p. case_id, '2017 _ '| lxid );
End if;
End loop;
Delete from dzjc_xzzf_t_zfgl where zfgl_id = zjid;
End loop;
Commit;
Return 'execution completed ';
End;