Oracle 树形SQL语句,SYS_CONNECT_BY_PATH 函数

原创 2007年09月15日 08:13:00

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

CONNECT_BY_ISLEAF伪列

在 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

 CONNECT_BY_ROOT伪列

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

select connect_by_root ,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

CONNECT_BY_ISCYCLE伪列

在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  

Oracle 树形SQL语句,SYS_CONNECT_BY_PATH 函数

SYS_CONNECT_BY_PATH 函数  自从Since Oracle 9i 开始,就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列...
  • kolabb
  • kolabb
  • 2014年02月06日 23:17
  • 525

Oracle之 SYS_CONNECT_BY_PATH 函数笔记

1、函数简介: 首先SYS_CONNECT_BY_PATH这个函数是oracle9i新提出的! 然后这个函数要和:START WITH(非必须) 和 CONNECT BY  PRIOR(必须)联合使用...
  • wxnjob
  • wxnjob
  • 2014年11月26日 11:32
  • 2309

mysql中实现类似oracle的SYS_CONNECT_BY_PATH功能

oracle中的SYS_CONNECT_BY_PATH函数为开发带来了便利,mysql中如何实现类似的功能呢? DELIMITER $$ CREATE FUNCTION `getParen...
  • guogang83
  • guogang83
  • 2014年04月02日 12:49
  • 2064

Oracle树查询及函数

Oracle树查询的最重要的就是select...start with... connect by...prior语法了。依托于该语法,我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Orac...
  • u014421556
  • u014421556
  • 2016年06月16日 10:04
  • 8191

SYS_CONNECT_BY_PATH函数用法

SELECT enameFROM scott.emp START WITH ename = KING CONNECT BY PRIOR empno = mgr; --得到结果为:KINGJONESSC...
  • wh62592855
  • wh62592855
  • 2009年10月29日 23:32
  • 6694

sys_connect_by_path函数(读书笔记)

/*sys_connect_by_path(column,clear),其中column是字符型或能自动转换成字符型的列名,它的主要目的就是将父节点到当前节点的“path”按照 指定的模式出现,ch...
  • u012366626
  • u012366626
  • 2014年09月08日 20:53
  • 1260

ORACLE的sys_connect_by_path的应用

转自:http://blog.csdn.net/seventh1984/archive/2009/02/26/3940168.aspx 个人觉得这个函数其实很好用,sys_connect_by_pat...
  • tianping168
  • tianping168
  • 2010年03月21日 23:18
  • 3665

荣工又解决问题了:用ORACLE的sys_connect_by_path函数,获得递归查询路径上各个节点的运算值

语句示例如下 Select JgId,JgType,OrgCode,Orgname,DepCode,DepName,ClsCode,ClsName,EtpCode,EtpName,Pre...
  • liyanchen78
  • liyanchen78
  • 2014年07月09日 19:22
  • 1050

Oracle之conncet by 之练习篇 sys_connect_by_path

10、sys_connect_by_path --sys_connect_by_path(字段名, 2个字段之间的连接符号) --其实SYS_CONNECT_BY_PATH这个函数是oracle9...
  • ystyaoshengting
  • ystyaoshengting
  • 2013年05月24日 15:46
  • 1497

oracle之sys_connect_by_path(行转列挺好用的)

这个函数使用之前必须先建立一个树,否则无用sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用repl...
  • lky5387
  • lky5387
  • 2009年12月09日 15:59
  • 6285
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 树形SQL语句,SYS_CONNECT_BY_PATH 函数
举报原因:
原因补充:

(最多只允许输入30个字)