记得以前在论坛里看到wildwave在回一个朋友的贴时候使用了start with,connect by。当时我一头雾水,从来没见过这两个关键字,网上搜了搜,讲的也不太详细,呵呵。今天看书的时候在目录中看到了,直接跳过前面内容翻到这个地方一睹为快。下面我就边做实验,边说说自己的学习成果吧。
实验中使用的表more_employees中的内容如下
1. 使用CONNECT BY和START WITH子句
SELECT语句中的CONNECT BY和START WITH子句的语法如下
SELECT [LEVEL],column,expression, ...
FROM table
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
其中:
- LEVEL是一个“伪列”,代表树的第几层。对根节点来说,LEVEL返回1,根节点的子节点返回2,以此类推。
- start_condition定义了层次化查询的起点。当编写层次化查询的时候必须指定START WITH子句。例如,可以将start_condition定义为employee=1,表示从员工#1开始。
- prior_condition定义了父行和子行的关系。当编写层次化查询时必须定义CONNECT BY PRIOR子句。例如,可以将prior_condition定义为employee_id=manager_id,表示父节点的employee_id和子节点的manager_id之间存在关系,也就是说,子节点的manager_id指向父节点的employee_id。
如下所示
2.使用伪列LEVEL
下面这个查询使用COUNT()和LEVEL来获取数中的层次数
3.格式化层次化查询的结果
可以用 LEVEL和LPAD函数对层次化查询结果进行格式化处理,方法是在数据的左边填补字符。如下例所示,根据不同LEVEL填充不同个数的空格,从而缩进显示员工的名字。
4.从非根节点开始遍历
对树进行遍历不一定要从根节点开始;使用START WITH子句可以从任何节点开始。下面这个查询就是从Susan Jones开始;注意,Susan Jones的LEVEL返回1,Jane Brown的LEVEL返回2,以此类推。
5.在START WITH中使用子查询
下面这个查询使用子查询来选择名为Kevin Black的员工的employee_id。然后传给START WITH子句。
6.从下向上遍历树
不一定非要按照从父节点到子节点的顺序从上至下遍历树;也可以从某个子节点开始,从下而上遍历。实现的方法是交换父节点和子节点在CONNECT BY PRIOR子句中的顺序。例如,CONNECT BY PRIOR manager_id=employee_id可以将父节点的manager_id连接到子节点的employee_id上。
下面这个查询从Jean Blue开始,向上遍历,直到James Smith为止;注意,Jean Blue的LEVEL返回1,John Grey的LEVEL返回2,以此类推。
7.从层次化查询中删除节点和分支
可以用WHERE子句从查询树中除去某个特定的节点,下面这个查询使用WHERE last_name!='Johnson'子句从结果中除去Ron Johnson
可以看到,尽管Ron Johnson已经从结果中除去了,但是他的下属Fred Hobbs和Rob Green仍然在结果中。为了将整个分支都从查询结果中除去,可以再CONNECT BY PRIOR子句中使用AND 子句。例如下面这个例子使用AND last_name!='Johnson'将Ron Johnson及其所有下属从结果中除去
8.在层次化查询中加入其它条件
使用WHERE子句可以在层次化查询中加入其它条件。下面这个例子使用WHERE子句来控制只显示工资少于等于$50000的员工
呵呵,累死了,好了差不多就这些了,以后碰到新的内容再做补充吧~ 吃饭 洗澡~
补充:
9、CONNECT BY 后面如果不加PRIOR的话,查询将不进行深层递归。
select * from t2 start with root_id = 0 connect by prior id = root_id;
|----------a-----------a1
| |
| |--------a2
|----------b-----------b1
| |
| |--------b2
select * from t2 start with root_id = 0 connect by id = root_id;
|-----------------a
|-----------------b
如果不加PRIOR关键字的话,就像上面所说到那样,不会进行深层次查询。
可以看到,都只显示了start with中指定的那一条记录而已。
10、运算符PRIOR被放置于等号前后的位置决定着查询时的检索顺序
下面我们看几个例子
=================================================================
使用SIBLINGS 关键字排序
前面说了,对于层次查询如果用order by 排序,比如order by last_name 则是先做完层次获得level, 然后按last_name 排序,这样破坏了层次,比如特别关注某行的深度,按level 排序,也是会破坏层次的。
在oracle10g 中,增加了siblings 关键字的排序。
语法:order siblings by <expre>
它会保护层次,并且在每个等级中按expre 排序。
select level,
id,last_name,manager_id
from s_emp
start with manager_id is null
connect by prior id=manager_id
order siblings by last_name;
结果如图:
CONNECT_BY_ISCYCLE 和NOCYCLE 关键字
如果从root 节点开始找其子孙,找到一行,结果发生和祖先互为子孙的情况,则发生循环,oracle 会报ORA-01436: CONNECT BY loop in user data ,在9i 中只能将发生死循环的不加入到树中或删除,在10g 中可以用nocycle 关键字加在connect by 之后,避免循环的参加查询操作。并且通过connect_by_iscycle 得到哪个节点发生循环。0 表示未发生循环,1 表示发生了循环,如:
create table family1(
fatherid number,
childid number
);
insert into family1 values(null,1);
insert into family1 values(1,2);-- 父节点为1
insert into family1 values(1,3);
insert into family1 values(2,4);-- 发生循环
insert into family1 values(4,1);-- 子节点为1
insert into family1 values(4,5);
commit;
select connect_by_iscycle, fatherid,childid,sys_connect_by_path(childid,'/')
from family1
start with fatherid is null
connect by nocycle prior childid=fatherid;
结果是: