【Wonder原创】Oracle字符串分割成Table函数

 

create or replace type t_SplitTable as object(
id integer,
valueStr varchar2(8000)
);

create or replace type t_Split_Table as table of t_SplitTable;

 

create or replace function Fun_SplitByDot
(
 p_str varchar2,
 p_char varchar2
)
 return t_Split_Table
as
 v_table t_Split_Table := t_Split_Table();
 
 v_num_id integer;
 v_var_data varchar2(2000);
 v_num_index  integer;
begin

  select nvl(p_str,'') into v_var_data from dual;
  if length(v_var_data)=0 then
    return v_table; --字符长度为0 ，退出
  end if;

  v_num_id  := 1;

    select instr(v_var_data,p_char) into v_num_index from dual;
  while(v_num_index>0)  
    loop
  
    v_table.extend();
    v_table(v_table.count) := t_SplitTable(v_num_id,substr(v_var_data,0,v_num_index-1));

        v_num_id := v_num_id+1;
        select substr(v_var_data,v_num_index+1,length(v_var_data)-v_num_index) into v_var_data from dual;
        select instr(v_var_data,p_char) into v_num_index from dual;
    end loop;
    if length(v_var_data)>0 then
    v_table.extend();
    v_table(v_table.count) := t_SplitTable(v_num_id,v_var_data);
    end if;

  return v_table;

end Fun_SplitByDot;

 

 

select * from table(fun_splitbydot('a,b,c',','));