PLSQL基础之阶层查询

1、ORACLEOg 新增了阶层查询操作符 PRIOR CONNECT_BY_ROOTP RIOR阶层查询的CONNECY BY condition的条件式需要用到 PRIOR来指定父节点，作为运算符，PRIOR和加(+)减(-)运算的优先级相同。阶层查询语法：START WITH con dition CONNECT BY NOCYCLE con dition START WITH指定阶层的根 CONNECT BY旨定阶层的父/子关系 NOCYCLE存在 CONNECT BY LOO的纪录时，也返回查询结果。或者 expr = P RIOR expr con dition . P RIOR ex

2、pr = expr例:CONNECT BY last_name != 'King' AND P RIOR emp loyee_id = manager_id .CONNECT BY P RIOR empi oyee_id = manager_id andP RIOR account_mgr_id = customer_id .CONNECT BY ROOT查询指定根的阶层数据。CONNECT B子句的例子通过CONNECT B子句定义职员和上司的关系。SQL>SELECT empio yee_id, last_name, manager_idFROM emp loyees

3、CONNECT BY P RIOR empI oyee_id = manager_id;EMPLOYEE ID LAST NAME MANAGER ID101 Kochhar100108 Greenberg 101109 Faviet 108110 Chen 108111 Sciarra 108112 Urman 108113 Popp 108200 Whalen 101LEVEL的例子通过LEVEL虚拟列表示节点的关系。SQL>SELECT empio yee_id, last_name, manager_id, LEVELFROM emp loyeesCONNECT BY P RIO

4、R empi oyee_id = manager_id;EMPLOYEE ID LAST NAME MANAGER ID LEVEL101 Kochhar100 1108 Greenberg 101 2109 Faviet 108 3110 Chen 108 3111 Sciarra 108 3112 Urman 108 3113 Popp 108 3START WITH子句的例子通过START WITH指定根节点， ORDER SIBLINGS BY保持阶层的顺序。SQL>SELECT last_name, empio yee_id, manager_id, LEVELFROM emp

5、 loyeesSTART WITH emp loyee_id = 100CONNECT BY P RIOR emp loyee_id = manager_idORDER SIBLINGS BY last_name;LAST NAME EMPLOYEE ID MANAGER ID LEVELKing 100 1Cambrault 148 100 2Bates 172 148 3Bloom 169 148 3Fox 170 148 3Kumar 173 148 3Ozer 168 148 3Smith 171 148 3De Haan 102 100 2Hunold 103 102 3Austin

6、 105 103 4Ernst 104 103 4Lorentz 107 103 4P ataballa 106 103 4Errazuriz 147 100 2Ande 166 147 3Banda 167 147 3John Russell的下属是部门80的管理者。hr.employees里，Steven King 是公司的最高责任者，没有上司，他有一个叫更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOPSQL>U PDATE emp loyees SET manager_id = 145WHERE emp loyee_id =

7、 100;SQL>SELECT last_name "Emp loyee".LEVEL, SYS_CONNECT_BY_ PATH(last_name, 7') "P ath"FROM emp loyeesWHERE level <= 3 AND dep artment_id = 80START WITH last_name = 'King'CONNECT BY P RIOR emp loyee_id=manager_id AND LEVEL <= 4;2 3 4 5 6 7 ERROR:ORA-01436: C

8、ONNECT BY loop in user dataCONNECT BY NOCYCLE 强制返回查询结果。 CONNECT BY ISCYCLE 显示 是否存在LOOP。SQL>SELECT last_name "Emp loyee", CONNECT_BY_ISCYCLE"Cycle",LEVEL, SYS_CONNECT_BY_ PATH(last_name, 7') "P ath"FROM emp loyeesWHERE level <= 3 AND dep artment_id = 80START WI

9、TH last_name = 'King'CONNECTBY NOCYCLE P RIOR emp loyee_id= manager_id AND LEVEL <=4;Emp loyee Cycle LEVEL P athRussell 1 2 /King/RussellTucker 0 3 /King/Russell/TuckerBernstein 0 3 /King/Russell/BernsteinHall 0 3 /King/Russell/HallOlsen 0 3 /King/Russell/OlsenCambrault 0 3 /King/Russell/

10、CambraultTuvault 0 3 /King/Russell/TuvaultP artners 0 2 /King/PartnersKing 0 3 /King/Partners/KingSully 0 3 /King/Partners/SullyMcEwen 0 3 /King/Partners/McEwenCONNECT BY ROOT例子 1、查询110部门的职员，上司，职员和上司之间级别差及路径。SELECT last_name "Em pl oyee", CONNECT_BY_ROOT last_name "Manager",LEVEL

11、-1 " Pathlen", SYS_CONNECT_BY_ PATH(last_name, 7') "P ath"FROM emp loyeesWHERE LEVEL > 1 and dep artment_id = 110CONNECT BY P RIOR empi oyee_id = manager_id;Empio yee Manager P athlen P athHiggins Kochhar 1 /Kochhar/HigginsGietz Kochhar 2 /Kochhar/Higgins/GietzGietz Higgins 1 /Higgins/GietzHiggins King 2 /King/Kochhar/HigginsGietz King 3 /King/Kochhar/Higgins/Gietz2、使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。SELECT name, SUM(salary) "Total_Salary" FROM (SELECT CONNECT_BY_ROOT last_name as name,