SYS_CONNECT_BY_PATH 函数及其他

SYS_CONNECT_BY_PATH 和几个伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE



SYS_CONNECT_BY_PATH 函数 

自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：
column path format a50
select level,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

LEVEL PATH 
-------- --------------------------------------------
1 /Asia
2 /Asia/China
3 /Asia/China/Beijing
2 /Asia/Japan
3 /Asia/Japan/Osaka
3 /Asia/Japan/Tokyo
1 /Australia
2 /Australia/New South Wales
3 /Australia/New South Wales/Sydney
1 /Europe
2 /Europe/United Kingdom
3 /Europe/United Kingdom/England
4 /Europe/United Kingdom/England/London
1 /North America
2 /North America/Canada
3 /North America/Canada/Ontario
4 /North America/Canada/Ontario/Ottawa
4 /North America/Canada/Ontario/Toronto
2 /North America/USA
3 /North America/USA/California
4 /North America/USA/California/Redwood Shores 

在 Oracle 10g  中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数 CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：

select connect_by_isleaf,sys_connect_by_path(child,"/" ) path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ISLEAF PATH
---------------------------------- ------------
0 /Asia
0 /Asia/China
1 /Asia/China/Beijing
0 /Asia/Japan
1 /Asia/Japan/Osaka
1 /Asia/Japan/Tokyo
0 /Australia
0 /Australia/New South Wales
1 /Australia/New South Wales/Sydney
0 /Europe
0 /Europe/United Kingdom
0 /Europe/United Kingdom/England
1 /Europe/United Kingdom/England/London
0 /North America
0 /North America/Canada
0 /North America/Canada/Ontario
1 /North America/Canada/Ontario/Ottawa
1 /North America/Canada/Ontario/Toronto
0 /North America/USA
0 /North America/USA/California
1 /North America/USA/California/Redwood Shores 

 

在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。

select connect_by_root child,sys_connect_by_path(child,"/" ) path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ROOT PATH
------------------------------ -------- 
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores 

在Oracle 10g  之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436:  CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定 “NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示： 

create table hier2
(
parent number,
child number
);

insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);

select connect_by_iscycle,sys_connect_by_path(child,"/" ) path
from hier2
start with parent is null
connect by nocycle prior child = parent;

CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3 

例如：  有一个 county 表 其中有parentid,id,name 分别代表父级编码,编码,地区名称

 select sys_connect_by_path(name,'/') from county t start with t.parentid is not null connect by prior id=parentid;

其中 start with t.prarentid is not null 是要遍历所有父节点不为空的所有节点；connect by prior id=parentid是指的父子关系，如果希望显示结果是由小到大，则可以 connect by prior parentid=id

语句的执行结果为：

--------------------------------------------------------------------------------

/浙江省
/浙江省/杭州市
/浙江省/杭州市/上城区
/浙江省/杭州市/下城区
/浙江省/杭州市/拱墅区
/浙江省/杭州市/西湖区
/浙江省/杭州市/萧山区
/浙江省/杭州市/余杭区
/浙江省/杭州市/淳安县
/浙江省/杭州市/建德市
/浙江省/杭州市/临安市
/浙江省/杭州市/市辖区
/浙江省/杭州市/江干区
/浙江省/杭州市/滨江区
/浙江省/杭州市/桐庐县