create or replace type t_ret_table is table of varchar2(20);
create or replace function f_test(var_num in integer) return t_ret_table is
var_out t_ret_table;
begin
var_out := t_ret_table();
var_out.extend(var_num);
for i in 1..var_num loop
var_out(i) := i;
end loop;
return var_out;
end f_test;
/
set serverout on
declare
aa t_ret_table;
begin
aa := f_test(10);
for i in 1..aa.count loop
dbms_output.put_line(aa(i));
end loop;
end;
/
一个分隔字符串的例子：
把'a|||b|||c'字符串分隔成
a
b
c
create or replace function f_test(var_str in string) return t_ret_table is
var_out t_ret_table;
var_tmp varchar2(2000);
var_element varchar2(2000);
begin
var_tmp := var_str;
var_out := t_ret_table();
while instr(var_tmp,'|||')>0 loop
var_element := substr(var_tmp,1,instr(var_str,'|||')-1);
var_tmp := substr(var_tmp,instr(var_str,'|||')+3,length(var_tmp));
var_out.extend(1);
var_out(var_out.count) := var_element;
end loop;
var_out.extend(1);
var_out(var_out.count) := var_tmp;
return var_out;
end f_test;
/
set serverout on
declare
aa t_ret_table;
begin
aa := f_test('a|||b|||c');
for i in 1..aa.count loop
dbms_output.put_line(aa(i));
end loop;
end;
/
create or replace function f_test(var_str in string) return t_ret_table PIPELINED
as
var_tmp varchar2(2000);
var_element varchar2(2000);
begin
var_tmp := var_str;
while instr(var_tmp,'|||')>0 loop
var_element := substr(var_tmp,1,instr(var_str,'|||')-1);
var_tmp := substr(var_tmp,instr(var_str,'|||')+3,length(var_tmp));
pipe row(var_element);
end loop;
pipe row(var_tmp);
return;
end f_test;
/
select * from table(f_test('a|||b|||c'));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18921899/viewspace-1017657/ ，如需转载，请注明出处，否则将追究法律责任。
