17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> create or replace function f_returntable(str1 in varchar2) return table_type
2 as
3 str varchar2(100);
4 t_1 table_type:=table_type();
5 begin
6 t_1:=table_type();
7 str:=','||str1||',';
8 for i in 1..(length(str)-length(replace(str,',',''))) loop
9 t_1.extend;
10 t_1(t_1.count):=obj_table(null,null);
11 t_1(t_1.count).id:=t_1.count;
12 t_1(t_1.count).col:=substr(str,instr(str,',',1,i)+1,instr(str,',',1,i+1)-instr(str,',',1,i)-1);
13 end loop;
14 return t_1;
15 end;
16 /
Function created
SQL>select * from table(f_returntable('a,b,cc'))
2 /
SQL> select * from table(f_returntable('a,b,cc'))
2 /
ID COL
---------- --------------------------------------------------------------------------------
1 a
2 b
3 cc
4