51,408
社区成员
发帖
与我相关
我的任务
分享oracle split函数
create or replace type type_split as table of varchar2(2000); --创建数组
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('测试一,测试二',','));
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('测试一,测试二',','));
...全文
请发表友善的回复…
发表回复
liufengyi2006 2011-07-20
- 打赏
- 举报
占位符
我嘞个去 2011-07-20
- 打赏
- 举报
皮皮 2011-07-20
- 打赏
- 举报
呵呵。接分就对了
luoluo11108 2011-07-20
- 打赏
- 举报
这个是分享的么？？？
