把字符串分割成字符数组

把字符串分割成字符数组

袁光东

 

首先需要定义一个类型

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;

 

测试函数

 

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;