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
