Oracle string splitting Function

The Oracle string splitting function needs to retain the previous batch insertion and deletion functions in the process of converting the data stored in the graphic system to the direct operation at the underlying layer of the database, this involves splitting input strings and then using the ORA Stored Procedure for batch processing. Record the string sharding function: www.2cto.com 1: create or replace function split_str (var_str in varchar2, 2: var_split in varchar2) 3: /*************************************** * ************* 4: note: execute the following statement to create a parameter of type 5: create or replace type t_ret_table is table of varchar2 (100) 6: 7: ** function name: split_str 8: 9: [name] [type] [description] 10: ** var_str varchar2 string to be split 11: ** var_split varchar2 string separator 12: ** return value: t_ret_table CURSOR data set after split 13: ** Abstract: Split string 14: 15: Call example: 16: select * from table (split_str ('A-B-C ', '-') 17: **************************************** * ***********/18: 19: return t_ret_table is 20: 21: var_out t_ret_table; 22: var_tmp varchar2 (4000); 23: var_element varchar2 (4000); 24: 25: begin www.2cto.com 26: var_tmp: = var_str; 27: var_out: = t_ret_table (); 28: -- if a matched separator 29: while instr (var_tmp, var_split)> 0 loop 30: var_element: = substr (var_tmp, 1, instr (var_tmp, var_split)-1); 31: var_tmp: = substr (var_tmp, 32: instr (var_tmp, var_split) + length (var_split ), 33: length (var_tmp); 34: -- var_out.extend (1); 35: var_out.extend; 36: 37: -- reset the var_element value 38: var_element: = substr (var_element, instr (var_element, '>', 1) + 1); 39: var_out (var_out.count): = var_element; 40: 41: -- print 42: -- set serverout on; 43: DBMS_OUTPUT.PUT_LINE (var_element); 44: -- print 45: 46: end loop; 47: 48: -- var_out.extend (1); 49: var_out.extend; 50: var_out (var_out.count): = var_tmp; 51: 52: return var_out; 53: end split_str; www.2cto.com