split函数 在oracle,oracle实现split函数

type char_table is table of varchar2(4000);

下面是具体的函数

function split_string(pi_str in varchar2, pi_separator in varchar2) return char_table is

v_char_table char_table;

v_temp varchar2(4000);

v_element varchar2(4000);

begin

v_char_table := char_table();

v_temp := pi_string;

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;

测试函数Sql代码 procedure test_split_string(pi_string in varchar, pi_separator in varchar2) is    v_char_table char_table   begin     v_char_table := split_string(pi_string, pi_separator);      for i in 1 .. v_char_table.count     loop          dbms_output.put_line(v_char_table(i));      end loop;   end test_split_string;