1、19Copyright Oracle Corporation, 2001. All rights reserved.分級查詢分級查詢19-2Copyright Oracle Corporation, 2001. All rights reserved.目標(biāo)目標(biāo)通過本章學(xué)習(xí)，您將可以通過本章學(xué)習(xí)，您將可以:分級查詢的概念分級查詢的概念創(chuàng)建樹形的報表創(chuàng)建樹形的報表格式劃分級數(shù)據(jù)格式劃分級數(shù)據(jù)在樹形結(jié)構(gòu)中刪除分支和節(jié)點在樹形結(jié)構(gòu)中刪除分支和節(jié)點19-3Copyright Oracle Corporation, 2001. All rights reserved.EMPLOYEES 表中的數(shù)據(jù)表中的數(shù)

2、據(jù)19-4Copyright Oracle Corporation, 2001. All rights reserved.樹形結(jié)構(gòu)樹形結(jié)構(gòu)EMPLOYEE_ID = 100 (雙親雙親)MANAGER_ID = 100 (孩子孩子)19-5Copyright Oracle Corporation, 2001. All rights reserved.分級查詢分級查詢 expr comparison_operator exprSELECT LEVEL, column, expr.FROM tableWHERE condition(s)START WITH condition(s)CONNECT

3、BY PRIOR condition(s) ;19-6Copyright Oracle Corporation, 2001. All rights reserved.遍歷樹遍歷樹始始 點點指定需要滿足的條件指定需要滿足的條件接受有效的條件接受有效的條件遍歷遍歷 EMPLOYEES 表表, 以姓名為以姓名為 Kochhar的員工作為始點的員工作為始點.START WITH last_name = KochharSTART WITH column1 = value19-7Copyright Oracle Corporation, 2001. All rights reserved.遍歷樹遍歷樹方向

4、方向CONNECT BY PRIOR column1 = column2. CONNECT BY PRIOR employee_id = manager_id19-8Copyright Oracle Corporation, 2001. All rights reserved.遍歷樹遍歷樹: 從底到頂從底到頂SELECT employee_id, last_name, job_id, manager_idFROM employeesSTART WITH employee_id = 101CONNECT BY PRIOR manager_id = employee_id ;19-9Copyrig

5、ht Oracle Corporation, 2001. All rights reserved.遍歷樹遍歷樹: 從頂?shù)降讖捻數(shù)降譙ELECT last_name| reports to | PRIOR last_name Walk Top DownFROM employeesSTART WITH last_name = KingCONNECT BY PRIOR employee_id = manager_id ;19-10Copyright Oracle Corporation, 2001. All rights reserved.使用使用 LEVEL 偽列標(biāo)記層次偽列標(biāo)記層次 層次層次1根

6、根/雙親雙親層次層次2雙親雙親/孩子孩子層次層次3雙親雙親/孩子孩子/葉子葉子層次層次4葉子葉子19-11Copyright Oracle Corporation, 2001. All rights reserved.使用使用 LEVEL 和和 LPAD格式化分層查詢格式化分層查詢COLUMN org_chart FORMAT A12SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,_) AS org_chartFROM employees START WITH last_name=King CONNECT BY PRIOR employ

7、ee_id=manager_id 19-12Copyright Oracle Corporation, 2001. All rights reserved.修剪樹枝修剪樹枝19-13Copyright Oracle Corporation, 2001. All rights reserved.總結(jié)總結(jié)通過本章學(xué)習(xí)通過本章學(xué)習(xí),您已經(jīng)可以您已經(jīng)可以: 對具有層次關(guān)系的數(shù)據(jù)創(chuàng)建樹形報表對具有層次關(guān)系的數(shù)據(jù)創(chuàng)建樹形報表指定遍歷的始點和方向指定遍歷的始點和方向刪除節(jié)點和樹枝刪除節(jié)點和樹枝19-14Copyright Oracle Corporation, 2001. All rights reserved.Hidden Slide19-15Copyright Oracle Corporation