oracle pipe row注意,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