CREATE OR REPLACE FUNCTION STR_TO_TB ( p_str varchar2, p_delim varchar2) return t_vc as
v_result t_vc;
v_delimlen number := length(p_delim);
i number := 1;
j number;
p integer; --pointer to string
p2 integer; --pointer to delim
begin
if p_str is null then
return null;
elsif p_delim is null then
return t_vc(p_str);
end if;
v_result := t_vc();
p := 1;
loop
p2 := instr(p_str, p_delim, p);
v_result.extend;
if p2 <> 0 then
v_result(i) := substr(p_str, p, p2 - p);
else
v_result(i) := substr(p_str, p);
exit;
end if;
-- check duplication value(检测重复的值)
--for j in 1..i loop
-- if j<>i and v_result(j)=v_result(i) then
-- raise_application_error(-20000, 'Duplicate value on '||j||' and '||i||' :'||v_result(i));
-- end if;
--end loop;
p := p2 + v_delimlen;
i := i + 1;
end loop;
return v_result;
end;
运行:select *
from table(cast(str_to_tb('2018-2019-1,2017-2018-2,2017-2018-1,2016-2017-2,2016-2017-1,2015-2016-2,2015-2016-1,2014-2015-2',
',') as t_vc))
结果:
2018-2019-1
2017-2018-2
2017-2018-1
2016-2017-2
2016-2017-1
2015-2016-2
2015-2016-1
2014-2015-2