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 |