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保持阶层的顺序。
hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。
更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。
■CONNECT_BY_ROOT的例子
1，查询110部门的职员，上司，职员和上司之间级别差及路径。
2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。
■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, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name; LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL -- ----------------------- ----------- ---------- ---------- King 100 1 Cambrault 148 100 2 Bates 172 148 3 Bloom 169 148 3 Fox 170 148 3 Kumar 173 148 3 Ozer 168 148 3 Smith 171 148 3 De Haan 102 100 2 Hunold 103 102 3 Austin 105 103 4 Ernst 104 103 4 Lorentz 107 103 4 Pataballa 106 103 4 Errazuriz 147 100 2 Ande 166 147 3 Banda 167 147 3
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
1145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
