17,377
社区成员
发帖
与我相关
我的任务
分享
--摘自asktom
--str2tbl, 将逗号分隔的字符串转成表
--考虑:写成piple row函数,可优化性能
create or replace type str2tblType as table of varchar2(30);
/
create or replace
function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
return str2tblType
as
l_str long default p_str || p_delim;
l_n number;
l_data str2tblType := str2tbltype();
begin
dbms_application_info.set_client_info( userenv('client_info')+1 );
loop
l_n := instr( l_str, p_delim );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data(l_data.count) := ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/
select *
from TABLE(cast( str2tbl( '1,2,3,4,5' ) as str2tblType) ) t
/
--这样也不行吗?
grant create any procedure to userA;