类似SQL中的split函数[@more@]
SQL > select * from table (split( ' northsnow,塞北的雪 ' , ' , ' ));
COLUMN_VALUE
-- ------------------------------------------------
northsnow
塞北的雪
create
or
replace
type type_split
as
table
of
varchar2
(
50
);
--
创建一个 type ,如果为了使split函数具有通用性,请将其size 设大些。
-- 创建function
create or replace function split
(
p_list varchar2 ,
p_sep varchar2 : = ' , '
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2 ( 50 ) : = p_list;
begin
loop
l_idx : = instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list, 1 ,l_idx - 1 ));
v_list : = substr(v_list,l_idx + length(p_sep));
else
pipe row(v_list);
exit ;
end if ;
end loop;
return ;
end split;
-- 创建function
create or replace function split
(
p_list varchar2 ,
p_sep varchar2 : = ' , '
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2 ( 50 ) : = p_list;
begin
loop
l_idx : = instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list, 1 ,l_idx - 1 ));
v_list : = substr(v_list,l_idx + length(p_sep));
else
pipe row(v_list);
exit ;
end if ;
end loop;
return ;
end split;
测试:
SQL > select * from table (split( ' northsnow,塞北的雪 ' , ' , ' ));
COLUMN_VALUE
-- ------------------------------------------------
northsnow
塞北的雪
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/101162/viewspace-965491/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/101162/viewspace-965491/