分类： 数据库开发技术
2007-01-05 20:16:46
Oracle的一个非常有趣但又很少使用或了解的功能是connect by子句。简单地说，它是报告家族树分支排序的一种方法。这样的树经常遇到：人类家庭、牲畜、马、社团管理、公司划分、制造、文学、概念、进化、科学研究、理论、甚至建立在视图上的视图。
在每棵树中，connect by提供了一个报告所有家族成员的方法。它允许排除一个家族的分枝或单个成员，也允许上、下遍历此树，以及报告在遍历过程中遇到的家族成员。
树中最早的祖先在技术上称为根节点(root node)。在日常英语中被称为树干(trunk)。从树干扩展出来的为树枝(branch)，每一个树枝又有其他树枝。从一个较大的分枝分裂出一个或多个树枝的分叉(fork)称为节点(node)。树枝的最末端称为树叶(1eaf)或树叶节点(1eaf node)。图12-1显示了一棵树的图。
下面是在1900年1月至1908年10月出生的奶牛和公牛的列表。出生的每个后代同其性别、双亲及出生日期等信息同在表中作为一行。如果用图12-1的表来比较奶牛及其后代，可以发现它们是对应的。表中EVE没有双亲，因为它是第一代，ADAM和BANDIT由人工喂养，因而也无双亲。
column Cow format a6
column Bull format a6
column Offspring format a10
column Sex format a 3
select * from BREEDING
order by Birthdate；
OFFSPRING SEX COW BULL BIRTHDATE
____________________________________________________________________________________________
下面这个查询语句直观地说明了—个家族的关系。这是通过使用LPAD函数和一特殊的列、级，与connect by一起来完成的。Level是一个从1(EVE)到5(DELLA)的数字，实际上表示代(generation)，DELLA就是第五代。无论何时使用connect by子句时， 这一Level列可被加入select语句中，从而找出每一行属于第几代。同SysDate
和User一样，level是一个伪列(pseudo co1umn)。它并不真是一个表的真实的一部分，但在特殊环境下可以使用。下面列出使用Level的一个例子。
这个查询的结果出现在下表中，但为什么select语句会得到这样的结果呢?它是怎样工作的呢?
select Cow，Bull，LPAD(' ',6*(Level-1))||Offspring Offspring,Sex，Birthdate
from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR Offspring；
COW BUlL OFFSPRING SEX BIRTHDATE
--------------------------------------------------------------
EVE F
EVE ADAM BETSY F 02_JAN_00
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06
EVE POCO CINDY F 09_FEB_03
注意，这实际上是图12-l按顺时针方向转到一边。EVE不在中心，但她是此树的根节点(trunk)。她的孩子有BETSY、POCO、GRETA、MANDY及CINDY。BETSY的孩子有NOVL、GINNY及TEDDI。GINNY的孩子有SU2Y和RUTH。SUZY的孩子有DELLA。MANDY也有两个孩子，它们是DUKE和PAULA。
这棵树从EVE开始作为第一代。如果SQL语句指定从MANDY开始，那么仅有MANDY、DUKE和PAULA被选出。start with定义了树开始显示的起点位置，且仅包括从start with指明的双亲分叉的树枝。startwith的作用正如它的名字所示。
select语句中的LPAD的结构很可能令人感到困惑。回顾一下第6章中LPAD的格式:
LPAD(string，length[,'set'])
比较一下这里与先前select语句中LPAD的区别:
LPAD(' '，6*(Level-1))
由空格分开的两个单引号没有定义用在填充中的字符，它是一个常量列，一个字符长的串。6*(Level-1)是长度(1ength)，因为set没有定义，使用缺省值，缺省值也为一空格。换句话说，它告诉SQL“定义一个字符空间，并用6*(Level-1)个空格从左边填充它”。那么这个数是什么呢?
6*(Level-1)
上面的算式首先从Level中减1。EVE的级数为1，所以1-1等于0。对于BETSY。它的Level(它的代数)为2，因此2-1为1。结果乘以6，这个数字表示与“左边”Offspring列连接的空格数。注意LPAD不直接填充Offspring，但与offspring并置。结果所显示的效果是明显的。每一个后代(generation)或级(1evel)左边按它的Level填充相应数目的空格。
为什么要填充和并置而不简单地直接在Offspring上使用LPAD呢？有两个原因：首先，在Offspring上直接使用LPAD将使得名字右对齐。每一级的名称将它们的最后字母的垂直排列起来。其次，如果Level-1等于零，如EVE，EVF使用LPAD的结果将是零字符宽。EVE将消失：
select Cow，Bull，LPAD(Offspring,6*(Level-1),' ') Offspring, Sex，Birthdate from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR Offspring；
COW BUlL OFFSPRING SEX BIRTHDATE
--------------------------------------------------------------
F
EVE ADAM BETSY F 02_JAN_00
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06
EVE POCO CINDY F 09_FEB_03
这样，为了得到每一级合适的位置，保证EVE显示出来，使得名字在左边垂直排列，应该和并置函数一起使用LPAD，而不是直接对Offspring列操作。
现在，connect by是怎样工作的呢?再看一下图12-1。从NOVI开始，向下遍历，哪一头奶牛是先于NOVI的一代(Offspring)?首先是BETSY。先于BETSY的一代则是EVE。尽管以下这个子句不是马上可以看懂的，这个子句是：
connect by Cow=PRIOR Offspring
它告诉SQL找到cow列中其值等于前行中的Offspring列的值的下一行。看一下表，读者就会发现这是正确的。
6.1 排除单一体及分枝
有两种方法可从一个报表中排除奶牛。一个是用通常的where子句技术，另一种是使用connect by子句。不同点在于使用connect by子句排除的不只是提到的奶牛，而且还包括它们所有的孩子。如果用connect by排除BETSY，那么NOVI、GINNY、TEDDI、SUZY、RUTH及DELLA都被排除。connect by实际上遍历树的结构。如果BETSY还没有出生，那么它的后代也就不会有，这个例子中，用and句子修改
connect by子句：
select Cow，Bull，LPAD(’’，6*(Level-1))||Offspring Offspring，Sex,Birthdate from BREEDING
start with Offspring='EVE’
connect by Cow=PRIOR Offspring
and Offspring!='BETSY'；
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
EVE F
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
BETSY BANDIT TEDDI F 12_AUG_05
EVE ADAM PETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
GINNY DUKE SUZY F 03_APR_06
GINNY DUKE RUTH F 25_DEC_06
MANDY BANDIT DUKE M 24_JUL_04
MANOY POCO PAULA F 21_DEC_06
SUZY BANDIT DELLA F 11_OCT_08
代(generations)是显而易见的，但Offspring和它们的母亲密
切地组合在一起。另一个查看相同家族树的方法是按生日，如下所示：
select Cow，Bull，LPAD(' '，6*(Level-1))||OffSpring OffSpring，Sex，Birthdate
from BREEDING
start with Offspring='EVE'
connect by Cow=PRIOR OffSpring Order by Binhdate；
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
EVE F
EVE ADAM BETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE BANDIT GRETA F 12_MAR_01
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
BETSY ADAM NOVI F 30_MAR_03
BETSY BANDIT GINNY F 04_DEC_03
MANDY BANDIT DUKE M 24_JUL_04
BETSY BANDIT TEDDI F 12_AUG_05
GINNY DUKE SUZY F 03_APR_06
MANDY POCO PAULA F 21_DEC_06
GINNY DUKE RUTH F 25_DEC_06
SUZY BANDIT DELLA F 11_OCT_08
现在行的顺序不像在树型结构中，不再表示代，但作为一棵树，仍然保留着这些信息。要了解哪个后代属于哪些双亲，就要查看Cow和Bull列。
6.2 遍历至根
到现在， 对家族树报表的遍历可以从父母到孩子。从一个孩子开始，移回到双亲、祖双亲、曾祖双亲等等，这可能吗?为了这样做，PRIOR将被移动到等号的另一边。下面跟踪DELLA的祖先：
select Cow，Bull，LPAD(' '，6*(Level-1))|| OffSpring OffSphng，
Sex,Birthdate
from BREEDING
start With OffSpring='DELLA’
connect by OffSpring=PRIOR Cow;
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
SUZY BANDIT DELLA F 11_OCT_08
GINNY DUKE SUZY F 03_APR_06
BETSY BANDIT GINNY F 04_DEC_03
EVE ADAM BETSY F 02_JAN_00
EVE F
这里显示了DELLA的根，但同先前显示的相比较，可能有些令人迷惑。这看起来DElLA是祖先，EVE是曾曾孙女。增加一个按生日排序的order by，但EVE仍在最右边：
select Cow，Bull,LPAD(' '，6*(Level-1))||OffSpring Offspring，Sex，Birthdate
from BREEDlNG
start with Offspring='DELLA’
connect by OffSpring=PRIOR Cow
order by Birthdate;
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
EVE F
EVE ADAM BETSY F 25_DEC_06
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUKE SUZY F 02_JAN_00
SUZY BANDIT DELLA F 11_OCT_08
解决的办法是简单地改变LPAD中的计算方法：
select Cow，Bull,LPAD(' '，6*(5-Level))|| OffSpring Offspring，Sex，Birthdate
from BREEDlNG
start with Offspring='DELLA’
connect by OffSpring=PRIOR Cow
order by Birthdate;
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
EVE F
EVE ADAM BETSY F 02_JAN_00
BETSY BANDIT GINNY F 04_DEC_03
GINNY DUCK SUZY F 03_APR_06
SUZY BANDIT DELLA F 11_OCT_08
最后，可以看出当用connect by跟踪公牛的双亲时，报表的变化是相当大的！下面是Adam的后代：
select Cow，Bull,LPAD(' '，6*(Level-1))|| OffSpring Offspring，Sex，Birthdate
from BREEDlNG
start with Offspring='ADAM’
connect by PRIOR OffSpring=Bull;
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
ADAM M
EVE ADAM BETSY F 02_JAN_00
EVE ADAM POCO M 15_JUL_00
EVE POCO MANDY F 22_AUG_02
EVE POCO CINDY F 09_FEB_03
MANDY POCO PAULA F 21_DEC_06
BETSY ADAM NOVI F 30_MAR_03
ADAM和BANDIT是牛群中最初的公牛。为了创建报告ADAM和BANDIT后代的单一树，必须为它们俩编造一个“父亲”，这个“父亲”可能是这棵树的根。这些覆盖前面介绍过的树类型的替换树的一个优点是，从家庭到项目到公司内部部门的分配，能够以多种方法正确地描述很多种继承组。
select Cow，Bull,LPAD(' '，6*(Level-1))|| OffSpring Offspring，Sex，Birthdate
from BREEDlNG
start with Offspring='BANDIT’
connect by PRIOR OffSpring=Bull;
COW BUlL OFFSPRING SEX BIRTHDATE
---------------------------------------------------------------
BANDIT M
EVE BANDIT GRETA F 12_MAR_01
BETSY BANDIT GINNY F 04_DEC_03
MANDY BANDIT DUKE M 24_JUL_04
GINNY DUKE SUZY F 03_APR_06
GINNY DUKE RUTH F 25_DEC_06
BETSY BANDIT TEDDI F 12_AUG_05
SUZY BANDIT DELLA F 11_OCT_08
6.3 基本规则
用connect by及start with建立树结构的报表并不难，但必须遵守下列规则：
.使用connect by时子句的顺序是
select
from
where
start with
connect by
order by
.prior使报表从树根到树叶（如果prior列是父母），或从树叶至树根（如果prior列是孩子）。
.where子句将从树中去掉单个节点，但保留她它们的后代（或祖先，假设PRIOR在等号的右边）。
.connect by中的限定（尤其是不等时）将去掉单个节点及其后代（或祖先，取决于浏览树的方向）。
.connect by不能用在where子句的连接表（table join）中。
这组特别的命令集只有少数人可能正确地记住。然而，有了对树及继承性的基本理解，在构造一个正确的select语句来报告一棵树时就不是一难事，可以参考本章来得到正确的语法。
EVE F ADAM M BANDIT M BETSY F EVE ADAM 02-JAN-00 POCO M EVE ADAM 15-JUL-00 GRETA F EVE BANDIT 12-MAR-01 MANDY F EVE POCO 22-AUG-02 CINDY F EVE POCO 09-FEB-03 NOVI F BETSY ADAM 30-MAR-03 GINNY F BETSY BANDIT 04-DEC-03 DUKE M MANDY BANDIT 24-JUL_04 TEDDI F BETSY BANDIT 12_AUG_05 SUZY F GINNY DUKE 03_APR_06 PAULA F MANDY POCO 21_DEC_06 RUTH F GINNY DUKE 25_DEC_06 DELLA F SUZY BANDIT 11_OCT_08
注：
关于双树的情况，比如person(树A),unit(树B)
树A属于树B
这时候涉及联合检索，例如：
select b.unit_level from person a,(select LEVEL as unit_level from unit start with unit_parent is null
connect by UNIT_CODE=PRIOR UNIT_PARENT_CODE) b
where a.UNIT_CODE = b.UNIT_CODE
connect by PERSON_PARENT_CODE =PRIOR PERSON_CODE
start with PERSON_PARENT_CODE is null order siblings by b.unit_level, a.CODE
如果你有其他问题，欢迎探讨。
