oracle层次化查询

查了一下，层次化查询有时也称connect by 查询，是oracle特有的特性，这类查询可以选取数据，并使用层次化的次序返回结果。

connect by 和start with  子句语法如下：

SELECT [LEVEL],COLUMN, expression，... FROM table [WHERE where_clause]  [[START WITH start_condition] 
[CONNECT BY PRIOR prior_condition]];

参数含义如下：

• level：表示一个伪列，代表树的第几层(可选)。
• from table：只能写一个table，从一个table从查询。
• where ：对返回结果进行了限定
• start_condition ：定义了层次化查询的起点，编写层次化查询时必须指定start with 子句
• prior_condition ： 定义了父行和子行之间的关系（上篇文章里已有描述），编写层次化查询时必须指定connect by  prior 子句

LEVEL:

level是存在于Oracle所执行的所有查询中的伪列，它是一个数值，可以指出节点在树中所在的层次，在层次化查询中，level值将起始的根节点作为层次1，如：

select  t.* , level, lpad('',4*level - 1) || name  from T_TEST_WORD  t  start  with pid='-1'  connect  by  prior  id = pid

示例：

用ｗｈｅｒｅ条件进行截取：

select  t.* , level, lpad('',4*level - 1) || name  from T_TEST_WORD  t where name != '无锡' start  with pid='-1'  connect  by  prior  id = pid

层次查询限制：

1.层次查询from 之后如果是table，只能是一个table，不能有join。

2.from之后如果是view，则view不能是带join的。

3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

以下内容摘自：http://blog.csdn.net/nsj820/article/details/6299276

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

    在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。

3、CONNECT_BY_ISCYCLE和NOCYCLE关键字

    如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环。

4、CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

5、使用SIBLINGS关键字排序

    对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

     在oracle10g中，增加了siblings关键字的排序。

语法：order  siblings  by <expre>

它会保护层次，并且在每个等级中按expre排序。