connect by

Oracle中connectby...startwith...的使用

一、语法

大致写法：select*fromsome_table[where条件1]connectby[条件2]startwith[条件3];

其中connectby与startwith语句摆放的先后顺序不影响查询的结果，[where条件1]可以不需要。

[where条件1]、[条件2]、[条件3]各自作用的范围都不相同：

[where条件1]是在根据“connectby[条件2]startwith[条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤，不会考虑树的结构；

[条件2]指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

[条件3]限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

示例：

假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。

select*fromsome_tabletwheret.id!=123connectbypriort.p_id=t.idandt.p_id!=321startwitht.p_id=33ort.p_id=66;

对prior的说明：

prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“startwith[条件3]”的记录，不会在寻找这些记录的子节点。要的时候有两种写法：connectbypriort.p_id=t.id或connectbyt.p_id=priort.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。

二、执行原理

connectby...startwith...的执行原理可以用以下一段程序的执行以及对存储过程RECURSE()的调用来说明：

/*遍历表中的每条记录，对比是否满足startwith后的条件，如果不满足则继续下一条，

如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点，

如此循环直到遍历完整个表的所有记录。*/

forrecin(select*fromsome_table)loop

ifFULLFILLS_START_WITH_CONDITION(rec)then

RECURSE(rec,rec.child);

endif;

endloop;

/*寻找子节点的存储过程*/

procedureRECURSE(recinMATCHES_SELECT_STMT,new_parentINfield_type)is

begin

APPEND_RESULT_LIST(rec);/*把记录加入结果集合中*/

/*再次遍历表中的所有记录，对比是否满足connectby后的条件，如果不满足则继续下一条，

如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点，

如此循环直到找至叶子节点。*/

forrec_recursein(select*fromsome_table)loop

ifFULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child,new_parent)then

RECURSE(rec_recurse,rec_recurse.child);

endif;

endloop;

endprocedureRECURSE;

三、使用探讨

从上面的执行原理可以看到“connectby...startwith...”构造树的方式是：（1）如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。

因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重复记录的异常情况

--1.HierarchicalQueries:STARTWITHandCONNECTBYPRIORclauses

--HierarchicalQueries

--STARTWITHandCONNECTBYPRIORclauses.

SELECTemployee_id,manager_id,first_name,last_name

FROMemployee_jh

STARTWITHemployee_id=1

CONNECTBYPRIORemployee_id=manager_id;

EMPLOYEE_IDMANAGER_IDFIRST_NAMELAST_NAME

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

10JamesSmith

21RonJohnson

32FredHobbs

52RobGreen

41SusanJones

64JaneBrown

96HenryHeyson

74JohnGrey

87JeanBlue

101KevinBlack

1110KeithLong

1210FrankHoward

1310DoreenPenn

13rowsselected.

--2.UsingaSubqueryinaSTARTWITHClause

SELECTLEVEL,

LPAD('',2*LEVEL-1)||first_name||''||last_nameASemployee

FROMemployee_jh

STARTWITHemployee_id=(SELECTemployee_idFROMemployee_jhWHEREfirst_name='Kevin'ANDlast_name='Black')

CONNECTBYPRIORemployee_id=manager_id;

LEVELEMPLOYEE

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

1KevinBlack

2KeithLong

2FrankHoward

2DoreenPenn

--3.IncludingOtherConditionsinaHierarchicalQuery

SELECTLEVEL,

LPAD('',2*LEVEL-1)||first_name||''||

last_nameASemployee,salary

FROMemployee_jh

WHEREsalary<=50000

STARTWITHemployee_id=1

CONNECTBYPRIORemployee_id=manager_id;

LEVELEMPLOYEESALARY

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

3RobGreen40000

3JaneBrown45000

4HenryHeyson30000

3JohnGrey30000

4JeanBlue29000

3KeithLong50000

3FrankHoward45000

3DoreenPenn47000

8rowsselected.