树结构查询.doc

树结构查询ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型结构的联系。例如，我们现在讨论雇员信息表EMP，其中含有雇员编号（EMPNO）和经理（MGR）两例，通过这两列反映出来的就是雇员之间领导和被领导的关系。有些雇员领导另一些雇员，有些雇员被领导，还有些雇员领导一些人又被别人领导，他们之间的这种关系就是一种树结构，图1.1表示了EMP表雇员间的这种树结构。KING EMPNO=7839MGR IS NULL MGR=7839 MGR=7839 MGR=7839 EMPNO=7566 EMPNO=9698 EMPNO=7782JONES BLAKE CLARKMGR=7566 MGR=7566 MGR=7698 MGR=7698 MGR=7698 MGR=7698 MGR=7698 MGR=7782SCOTT FORD ALLEN WARD MARTIN TURNER JAMES MILLER EMPNO=7788 EMPNO=7902 EMPNO=7499 EMPNO=7521 EMPNO=9654 EMPNO=7844 EMPNO=7900 EMPNO=7934 MGR=7788 MGR=7902ADAMS SMITHEMPNO=7876 EMPNO=7369图1.1 EMP表树结构图 在这个树结构中，如果一个节点有直接的下属节点（如图中的JONES 有碍SCOTT 和FORD），那么称该节点是下属节点的父节点，下属节点为该节点的子节点。通过雇员的EMPNO和MGR可以看出他们之间的父子节点关系，父节点的EMPNO与子节点的MGR相同。在树结构中，有且仅有一个节点无父节点，如图中的KING，该节点被称为根节点。从图上的标记可以看出，只有KING的MGR为空值。除根节点外，任何节点只有一个父节点，有一个，多个或没有子节点。早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：第一步：从根节点开始；第二步：访问该节点；第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；第四步：若该节点为根节点，则访问完毕，否则执行第五步；第五步：返回到该节点的父节点，并执行第三步骤。总之：扫描整个树结构的过程也即是中序遍历树的过程。1 树结构的描述树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：SELECT 。CONNECT BY PRIOR 列名1=列名2|列名1=PRIOR 裂名2START WITH；其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。例1 以树结构方式显示EMP表的数据。SQL SELECT EMPNO,ENAME，MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME=KING;EMPNOENAMEMGR7839 KING7566JONES78397788SCOTT75667876ADAMS77887902FORD75667369SMITH79027698BLAKE78397499ALLEN76987521WARD76987654MARTIN76987844TURNER76987900JAMES76987782CLARK78397934MILLER778214 rows selected.2 关于PRIOR运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：CONNECT BY PRIOR EMPNO=MGRPIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：CONNECT BY EMPNO=PRIOR MGR 在这种方式中也应指定一个开始的节点。例2 从SMITH节点开始自底向上查找EMP的树结构。SQLSELECT EMPNO，ENAME，MGR FROM EMP CONNECT BY EMPNO=PRIOR MGR START WITH ENAME=SMITH； EMPNOENAMEMGR 7369SMITH79027902FORD75667566JONES78397839 KING 4 rows selected.在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中每一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其父节点，再由其父节点向上，找父节点的父节点。这样一直找到根节点为止，结果就是树中一枝的数据。备注：例2的另外一种写法SELECT EMPNO,ENAME,MGRFROM EMPCONNECT BY PRIOR MGR=EMPNOSTART WITH ENAME= SMITH ;3 定义查找起始节点在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。例3 查找JONES直接或间接领导的所有雇员信息。SQLSELECT EMPNO，ENAME，MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME=JONES； EMPNOENAMEMGR7566JONES78397788SCOTT75667876ADAMS77887902FORD75667369SMITH79025 rows selected.START WITH 不但可以指定一个根节点，还可以指定多个根节点。例4 查找由FORD和BLAKE 领导的所有雇员的信息。 SQLSELECT EMPNO，ENAME，MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR START WITH ENAME IN (FORD,BLAKE)；EMPNOENAMEMGR7698BLAKE78397499ALLEN76987521WARD76987654MARTIN76987844 TURNER76987900JAMES76987902FORD75667369SMITH79028 rows selected.在自底向上查询树结构时,也要指定一个开始节点，以此开始向上查找其父节点，直至找到根节点，其结果将是结构树中的一枝数据。4使用LEVEL在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。 层号KING 1JONES BLAKE CLARK 2 MILLER 3SCOTT FORD ALLEN WARD MARTIN TURNER JAMESADAMS SMITH 4图案1.2 EMP表树结构层次图在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次，我们可以使用LEVEL来控制对树型结构进行遍历的深度。例5显示EMP表中的各行数据及层号。 SQLSELECT LEVEL，EMPNO，MGRFROM EMPCONNECT BY PRIOR EMPNO=MGRSTART WITH ENAME=KING；LEVELEMPNOENAMEMGR17839KING27566JONES783937788SCOTT756647876ADAMS778837902FORD756647369SMITH790227698BLAKE783937499ALLEN769837521WARD769837654MARTIN769837844TURNER769837900JAMES769827782CLARK783937934MILLER778214 rows selected.伪列LEVEL为数值型，可以在SELECT 命令中用语各种计算机。例6 使用LEVEL改变查询结果的显示形式。SQLCOLUMN EMPLOYEE FORMAT A20SQL SELECT EMPNO,RPAD( ,LEVEL*3) |ENAME EMPLOYEE,MGR FROM EMP CONNECT BY PRIORY EMPNO=EMR START WITH ENAME=KING;EMPNOEMPLOYEEMGR7840 KING7566 JONES78397788 SCOTT75667876 ADAMS77887902 FORD75667369 SMITH79027698 BLAKE78397499 ALLEN76987521 WARD76987654 MARTIN76987844 TURNER76987900 JAMES76987782 CLARK78397934 MILLER778214 rows selected.在SELECT使用了函数RPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各自的层号确定，空格再与雇员名字拼接，结果显示出这种层次关系，也就是说其雇员名字右侧填充的空格数与它的层数有关。5节点和分支的裁剪在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。SQLCOLUMN EMPLOYEE FORMAT A20SQL SELECT EMPNO,RPAD( ,LEVEL*3) |ENAME EMPLOYEE,MGR FROM EMP WHERE ENAME!=SCOTT CONNECT BY PRIORY EMPNO=EMR START WITH ENAME=KING;EMPNOEMPLOYEEMGR7841 KING7566 JONES78397876 ADAMS77887902 FORD75667369 SMITH79027698 BLAKE78397499 ALLEN76987521 WARD76987654 MARTIN76987844 TURNER76987900 JAMES76987782 CLARK78397934 MILLER778213 rows selected.在这个查询中，仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支，则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。例8显示KING领导下的全体雇员信息，除去SCOTT领导的一支。SQL SELECT EMPNO,RPAD( ,LEVEL*3) |ENAME EMPLOYEE,MGR FROM EMP CONNECT BY PRIORY EMPNO=EMR AND ENAME!=SCOTT START WITH ENAME=KING;EMPNOEMPLOYEEMGR7842 KING7566 JONES78397902 FORD75667369 SMITH79027698 BLAKE78397499 ALLEN76987521 WARD76987654 MARTIN76987844 TURNER76987900 JAMES76987782 CLARK78397934 MILLER778212 rows selected.这个查询结果就与例7不同，除了剪去单个节点SCOTT外，还将SCOTT的子节点ADAMS剪掉，即把SCOTT这个分支剪掉了。当然WHERE子句可以和CONNECT BY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。例9显示KING领导全体雇员信息，除去雇员SCOTT，以及BLAKE领导的一支。SQLCOLUMN EMPLOYEE FORMAT A20SQL SELECT EMPNO,RPAD( ,LEVEL*3) |ENAME EMPLOYEE,MGR FROM EMP WHERE ENAME!=SCOTT CONNECT BY PRIORY EMPNO=EMR AND ENAME!=BLAKE START WITH ENAME=KING;EMPNOEM