PL/SQL基礎：階層查詢
2024-07-21 02:32:32
供稿：網友
Oracle 10g新增了階層查詢操作符PRIOR，CONNECT_BY_ROOT
■PRIOR
階層查詢的CONNECY BY condition的條件式需要用到PRIOR來指定父節點，
作為運算符，PRIOR和加(+)減(-)運算的優先級相同。
■階層查詢
語法：START WITH condition CONNECT BY NOCYCLE condition
START WITH 指定階層的根
CONNECT BY 指定階層的父/子關系
NOCYCLE 存在CONNECT BY LOOP的紀錄時，也返回查詢結果。
condition ... PRIOR eXPr = expr 或者 ... expr = PRIOR expr
例：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and
PRIOR account_mgr_id = customer_id ...
■CONNECT_BY_ROOT
查詢指定根的階層數據。
■CONNECT BY子句的例子
通過CONNECT BY子句定義職員和上司的關系。
SQL>SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101
■LEVEL的例子
通過LEVEL虛擬列表示節點的關系。
SQL>SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ----------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
■START WITH子句的例子
通過START WITH指定根節點，ORDER SIBLINGS BY保持階層的順序。
SQL>SELECT last_name, employee_id, manager_id, LEVELFROM employeesSTART WITH employee_id = 100CONNECT BY PRIOR employee_id = manager_idORDER SIBLINGS BY last_name;LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL------------------------- ----------- ---------- ---------- King 100 1Cambrault 148 100 2Bates 172 148 3Bloom 169 148 3Fox 170 148 3Kumar 173 148 3Ozer 168 148 3Smith 171 148 3De Haan 102 100 2Hunold 103 102 3Austin 105 103 4Ernst 104 103 4Lorentz 107 103 4Pataballa 106 103 4Errazuriz 147 100 2Ande 166 147 3Banda 167 147 3
hr.employees里，Steven King是公司的最高責任者，沒有上司，他有一個叫John Russell的下屬是部門80的治理者。
更新employees表，把Russell設置成King的上司，這樣就產生了CONNECT BY LOOP。
SQL>UPDATE employees SET manager_id = 145WHERE employee_id = 100;SQL>SELECT last_name "Employee",LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employeesWHERE level <= 3 AND department_id = 80START WITH last_name = 'King'CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;2 3 4 5 6 7 ERROR:ORA-01436: CONNECT BY loop in user dataCONNECT BY NOCYCLE強制返回查詢結果。CONNECT_BY_ISCYCLE顯示是否存在LOOP。SQL>SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employeesWHERE level <= 3 AND department_id = 80START WITH last_name = 'King'CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;Employee Cycle LEVEL Path------------------------- ------ ------ ------------------------- Russell 1 2 /King/RussellTUCker 0 3 /King/Russell/TuckerBernstein 0 3 /King/Russell/BernsteinHall 0 3 /King/Russell/HallOlsen 0 3 /King/Russell/OlsenCambrault 0 3 /King/Russell/CambraultTuvault 0 3 /King/Russell/TuvaultPartners 0 2 /King/PartnersKing 0 3 /King/Partners/KingSully 0 3 /King/Partners/SullyMcEwen 0 3 /King/Partners/McEwen
■CONNECT_BY_ROOT的例子
1，查詢110部門的職員，上司，職員和上司之間級別差及路徑。
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employeesWHERE LEVEL > 1 and department_id = 110CONNECT BY PRIOR employee_id = manager_id;Employee Manager Pathlen Path--------------- ------------ ---------- ----------------------------------- Higgins Kochhar 1 /Kochhar/HigginsGietz Kochhar 2 /Kochhar/Higgins/GietzGietz Higgins 1 /Higgins/GietzHiggins King 2 /King/Kochhar/HigginsGietz King 3 /King/Kochhar/Higgins/Gietz
2，使用GROUP BY語句，查詢110部門的職員以及該職員下屬職員的工資和。
SELECT name, SUM(salary) "Total_Salary" FROM (SELECT CONNECT_BY_ROOT last_name as name, SalaryFROM employeesWHERE department_id = 110CONNECT BY PRIOR employee_id = manager_id)GROUP BY name;NAME Total_Salary------------------------- ------------ Gietz 8300Higgins 20300King 20300Kochhar 20300
