Oracle pipe row 将一个字符串拆分成多条记录

create or replace function row_split(var_str in string, var_split In String)
  return t_ret_table
  PIPELINED as
  var_tmp     varchar2(4000);
  var_element varchar2(4000);
  n_length    Number := length(var_split);
begin
  var_tmp := var_str;
  while instr(var_tmp, var_split) > 0 loop
    var_element := substr(var_tmp, 1, instr(var_tmp, var_split) - 1);
    var_tmp     := substr(var_tmp,
                          instr(var_tmp, var_split) + n_length,
                          length(var_tmp));
    pipe row(var_element);
  end loop;
  pipe row(var_tmp);
  return;
end row_split;

测试：

select * from table(row_split(area_str, ','))

效果：

	COLUMN_VALUE
1	111
2	222
3	333