PL/SQL基础:阶层查询

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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, 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航