最简单的方法可能是编写一个存储的pl / sql函数,但是可以单独使用sql(Oracle)来完成。
这将分解子路径中的路径:
sql> variable path varchar2(4000);
sql> exec :path := 'a/b/c/def';
PL/sql procedure successfully completed
sql> SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path, ROWNUM rk
2 FROM dual
3 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1;
SUB_P RK
----- --
a 1
b 2
c 3
def 4
然后,我们使用来重构反向路径sys_connect_by_path
:
sql> SELECT MAX(sys_connect_by_path(sub_path, '/')) reversed_path
2 FROM (SELECT regexp_substr(:path, '[^/]+', 1, ROWNUM) sub_path,
3 ROWNUM rk
4 FROM dual
5 CONNECT BY LEVEL <= length(regexp_replace(:path, '[^/]', '')) + 1)
6 CONNECT BY PRIOR rk = rk + 1
7 START WITH rk = length(regexp_replace(:path, '[^/]', '')) + 1;
REVERSED_PATH
-------------
/def/c/b/a