资源描述：
《oracle和db的递归查询》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、动机当把应用程序从Oracle移植或迁移到DB2UDBforLinux、UNIX和Windows时，递归查询成为一块巨大的绊脚石。能理解和使用Oracle型递归的开发人员，却常常无法自然而然地理解DB2中的递归，反过来也是一样。乍一看来，这似乎是因为DB2是按照SQL标准中的定义并使用公共表表达式（CTE）和UNIONALL来实现递归的，而Oracle则使用CONNECTBY子句和一组所谓的伪列（pseudocolumn）及系统过程来定义递归。然而，两者之间的差别不仅在于语法方面。在底层，DB2处理递归时采用的方法是逐层递归（广度优先），而Oracle采用的是深度优先的方法。后一
2、种方法自然地产生与组织结构图（orgchart）相匹配的输出。在本文中，我使用一个实用的例子，这个例子将CONNECTBY和相关的伪列与DB2递归逐步进行匹配。设置例子首先，我们将定义和填充一个雇员表。这个表有四个列，一个是作为主键的雇员ID，一个是将雇员与其经理关联起来的经理ID，此外还有姓名和薪水：1CREATETABLEemp(empidINTEGERNOTNULLPRIMARYKEY,2nameVARCHAR(10),3salaryDECIMAL(9,2),4mgridINTEGER);5INSERTINTOemp6VALUES(1,'Jones',30000,10),7
3、(2,'Hall',35000,10),8(3,'Kim',40000,10),9(4,'Lindsay',38000,10),10(5,'McKeough',42000,11),11(6,'Barnes',41000,11),12(7,'O''Neil',36000,12),13(8,'Smith',34000,12),14(9,'Shoeman',33000,12),15(10,'Monroe',50000,15),16(11,'Zander',52000,16),17(12,'Henry',51000,16),18(13,'Aaron',54000,15),19(14,'S
4、cott',53000,16),20(15,'Mills',70000,17),21(16,'Goyal',80000,17),22(17,'Urbassek',95000,NULL);注意被插入的这些列，显然雇员‘Urbassek’是最高级的经理，‘Goyal’和‘Mills’是他的属下。而‘Scott’又是‘Goyal’的属下，但是他没有自己的属下。‘Monroe’则是‘Hall’、‘Kim’和‘Lindsay’的上司，同时也是‘Mills’的属下。一个合情合理的问题是：“谁是‘Goyal’直接或间接的属下？”回页首一个简单的递归查询为了回答谁是‘Goyal’的属下这个问题，
5、Oracle开发人员可能会编写以下查询：1SELECTname2FROMemp3STARTWITHname='Goyal'4CONNECTBYPRIORempid=mgridSTARTWITH表示递归的种子，而CONNECTBY描述递归步骤，也就是如何从第n步发展到第(n+1)步。由于在归结name时需要区分第n步和第(n+1)步，因此使用PRIOR来表明empid属于第n步，而mgrid属于第(n+1)步。在第1步，empid为16，mgrid也必须是16，因此第2步产生‘Scott’、‘Henry’和‘Zander’。他们的empid将作为第3步的PRIOR，依此类推。Ora
6、cle语法非常简洁。SQL标准和DB2语法不使用任何递归专用的关键词，而是使用常规的SQL来描述完全相同的关系。您会看到，这样做显得更啰嗦，但是同样也比较简单：1WITHn(empid,name)AS2(SELECTempid,name3FROMemp4WHEREname='Goyal'5UNIONALL6SELECTnplus1.empid,nplus1.name7FROMempasnplus1,n8WHEREn.empid=nplus1.mgrid)9SELECTnameFROMn;WITH子句允许在一条语句中定义一个有名称的查询，在同一条语句中，稍后将引用到这个查询。用技术
7、术语来说，这叫做公共表表达式（commontableexpression，CTE）。CTE现在在大多数基于SQL的DBMS中都受到支持，包括Oracle9i和SQLServer2005，当然也包括DB2。这个CTE的特殊之处在于，它在自己的定义中被引用。这就是常规CTE与递归CTE的区别。这里我将CTE命名为n，以便与递归步骤关联起来。递归CTE由两部分组成，这两部分通过UNIONALL连接：1.一部分是递归的种子（即第1步）。这就是在Oracle中用STARTWITH描述的那一
