大阳城集团娱乐网2138欢迎您!
大阳城集团娱乐网2138 > 计算机数据 > 阶层查询

阶层查询

时间:2020-01-12

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子句定义职员和上司的关系。 SQLSELECT 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虚拟列表示节点的关系。 SQLSELECT 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保持阶层的顺序。

SQLSELECT 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。

SQLUPDATE employees SET manager_id = 145 WHERE employee_id = 100; SQLSELECT 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。 SQLSELECT 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

上一篇:如何以事物的方式对数据库进行访问 下一篇:Oracle应用开发的几个问题