with t as (select 'org:AA\DFFDS\SDFSDF\SDFDF' n from dual),
t1 as (select substr(n,1,instr(n,':')) n1,substr(n,instr(n,':')+1) n2 from t)
select n1||regexp_substr(n2,'[^\]',1,level) as nn from t1 connect by level<=length(n2)-length(regexp_replace(n2,'\\','')+1;
没太看懂意思.根据我自己的理解吧.
传进去一列的话就先拆分(拆分办法在最下面),然后用
select * from table(f_orgurl('org:AA\')) union all
select * from table(f_orgurl('org:AA\DFFDS\')) union all
select * from table(f_orgurl('org:AA\DFFDS\SDFSDF\')) union all
select * from table(f_orgurl('org:AA\DFFDS\SDFSDF\SDFDF\'))得到你的结果集
--拆分
declare
str varchar2(4000) := 'org:AA\DFFDS\SDFSDF\SDFDF';
type cursors is ref cursor;
cur cursors;
o_out varchar2(4000);
begin
if substr(str, -1) <> '\' then
str := str || '\';
end if;
open cur for
select substr(str, 1, lev) str
from (select str, instr(str, '\', 1, level) lev
from (select str from dual)
connect by level <= REGEXP_COUNT(str, '[^\]+'));
loop
fetch cur
into o_out;
exit when cur%notfound;
dbms_output.put_line(o_out);
end loop;
close cur;
end;