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  

MySql的sql语句中添加存储过程或者存储函数来实现Oracle中的start with ……connect by prior……递归(树形结构数据)查询

的撒旦撒旦啊大大啊

SYS_CONNECT_BY_PATH 函数用法 ORACLE

SELECT ename   FROM scott.emp    START WITH ename = 'KING'    CONNECT BY PRIOR empno = mgr; ...

oracle sys_connect_by_path 函数

转自:IT pub   http://www.itpub.net/thread-838127-1-1.html 个人觉得这个函数其实很好用,sys_connect_by_path(字段名, ...

sys_connect_by_path函數的用法

Oracle之sys_connect_by_path行转列的使用 这个函数使用之前必须先建立一个树,否则无用 sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的...

wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

构建测试表: Sql代码 create table TABLE1   (     ID   INTEGER,     NAME VARCHAR2(10)   )     crea...

sys_connect_by_path函数配合group by 进行分组拼接

最近,碰到一个需求将 approval_code值对应的多个FIRST_NAME值通过line_no的asc排序 合并为一个最长的字段  ,对应的表 如下: 对应表的sql 语句如下: ...

sys_connect_by_path

http://blog.csdn.net/zwhfyy/article/details/4057742 个人觉得这个函数其实很好用,sys_connect_by_path(字段名, 2个字段之间的连...

HIERARCHICAL QUERIES AND SYS_CONNECT_BY_PATH

Hierarchical Queries If a table contains hierarchical data, then you can select rows in a hierarc...

SYS_CONNECT_BY_PATH

查询父类的语句: 我项目主要是用在了BOM, 输入行的item segment1, 查询出来哪个BOM在用它。   Oracle函数:sys_connect_by_path 主要用于树查询(层次...

sys_connect_by_path 的用法

个人觉得这个函数其实很好用,sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 树形SQL语句,SYS_CONNECT_BY_PATH 函数
举报原因:
原因补充:

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