hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。
更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。
SQL>UPDATE employees SET manager_id = 145
WHERE employee_id = 100;
SQL>SELECT last_name "Employee",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START 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 data
CONNECT BY NOCYCLE强制返回查询结果。CONNECT_BY_ISCYCLE显示是否存在LOOP。
SQL>SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE level <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;
Employee Cycle LEVEL Path
------------------------- ------ ------ -------------------------
Russell 1 2 /King/Russell
Tucker 0 3 /King/Russell/Tucker
Bernstein 0 3 /King/Russell/Bernstein
Hall 0 3 /King/Russell/Hall
Olsen 0 3 /King/Russell/Olsen
Cambrault 0 3 /King/Russell/Cambrault
Tuvault 0 3 /King/Russell/Tuvault
Partners 0 2 /King/Partners
King 0 3 /King/Partners/King
Sully 0 3 /King/Partners/Sully
McEwen 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 employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id;
Employee Manager Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz 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, Salary
FROM employees
WHERE department_id = 110
CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
NAME Total_Salary
------------------------- ------------
Gietz 8300
Higgins 20300
King 20300
Kochhar 20300 |
【相关文章】
