最后，我们来看看在ORACLE DATABASE 10G里对于分等级查询功能的增强。
Oracle Database 10g给我们带来了一些新特性。这些新特性包括CONNECT_BY_ROOT操作符，CONNECT_BY_ISCYCLE 和 CONNECT_BY_ISLEAF伪列，以及NOCYCLE关键字。
CONNECT_BY_ROOT操作符可以从根行取数据。举个例子，我们想查询每个用户及他们对应部门的经理名，可以如下考虑：
SELECT level, lname "Employee" ,dept_id,CONNECT_BY_ROOT lname "Top Manager"
FROM employee
START WITH emp_id in
(SELECT emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY manager_emp_id = PRIOR emp_id
AND dept_id != PRIOR dept_id)
CONNECT BY PRIOR emp_id = manager_emp_id
AND dept_id = PRIOR dept_id;
LEVEL Employee DEPT_ID Top Manager
---------------------- -------------------- ---------------------- --------------------
1 JONES 20 JONES
2 SCOTT 20 JONES
3 ADAMS 20 JONES
2 FORD 20 JONES
3 SMITH 20 JONES
1 BLAKE 30 BLAKE
2 ALLEN 30 BLAKE
2 WARD 30 BLAKE
2 MARTIN 30 BLAKE
2 TURNER 30 BLAKE
2 JAMES 30 BLAKE
1 KING 10 KING
2 CLARK 10 KING
3 MILLER 10 KING
14 rows selected
首先，我们找出所有的部门经理的emp_id，然后我们把查询结果作为下个查询的根，查询出我们想要的结果，注意CONNECT_BY_ROOT lname "Top Manager"，它直接返回了部门经理的lname，而不是各个员工主管经理的名字。
在一个树形结构中，循环是不允许的，但是在真实世界中，可能会出现这种情况，下面我们考虑修改我们的事例数据，
UPDATE employee
SET manager_emp_id
= 7654
WHERE manager_emp_id IS NULL;
现在我们的数据中存在一个循环，如果运行一个如下的查询，我们会得到一个错误ORA-01436：
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname "EMPLOYEE",
emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;
LEVEL EMPLOYEE EMP_ID MANAGER_EMP_ID
---------- -------------------- ---------- --------------
1 KING 7839 7654
2 JONES 7566 7839
3 SCOTT 7788 7566
4 ADAMS 7876 7788
3 FORD 7902 7566
4 SMITH 7369 7902
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 WARD 7521 7698
3 MARTIN 7654 7698
4 KING 7839 7654
5 JONES 7566 7839
6 SCOTT 7788 7566
7 ADAMS 7876 7788
6 FORD 7902 7566
ERROR:
ORA-01436: CONNECT BY loop in user data
15 rows selected. |
在CONNECT BY子句中使用NOCYCLE关键字可以避免这个错误消息，如下：
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname "EMPLOYEE",
emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY NOCYCLE PRIOR emp_id = manager_emp_id;
LEVEL EMPLOYEE EMP_ID MANAGER_EMP_ID
---------- -------------------- ---------- --------------
1 KING 7839 7654
2 JONES 7566 7839
3 SCOTT 7788 7566
4 ADAMS 7876 7788
3 FORD 7902 7566
4 SMITH 7369 7902
2 BLAKE 7698 7839
3 ALLEN 7499 7698
3 WARD 7521 7698
3 MARTIN 7654 7698
3 TURNER 7844 7698
3 JAMES 7900 7698
2 CLARK 7782 7839
3 MILLER 7934 7782 |
识别循环CONNECT_BY_ISCYCLE伪列，返回1说明存在一个循环。
SELECT lname, CONNECT_BY_ISCYCLE
FROM employee
START WITH emp_id = 7839
CONNECT BY NOCYCLE PRIOR emp_id = manager_emp_id;
LNAME CONNECT_BY_ISCYCLE
-------------------- ------------------
KING 0
JONES 0
SCOTT 0
ADAMS 0
FORD 0
SMITH 0
BLAKE 0
ALLEN 0
WARD 0
MARTIN 1
TURNER 0
JAMES 0
CLARK 0
MILLER 0 |
识别叶节点，CONNECT_BY_ISLEAF，返回值为1则为叶节点。
SELECT lname, CONNECT_BY_ISLEAF
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;
LNAME CONNECT_BY_ISLEAF
--------------- -----------------
KING 0
JONES 0
SCOTT 0
ADAMS 1
FORD 0
SMITH 1
BLAKE 0
ALLEN 1
WARD 1
MARTIN 1
TURNER 1
JAMES 1
CLARK 0
MILLER 1 |
posted on 2007-10-12 10:44
tianjuchuan 阅读(396)
评论(0) 编辑 收藏 引用 所属分类:
sql