CREATE TABLE employee(
empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
salary DECIMAL(9, 2),
mgrid INTEGER
);
INSERT INTO employee VALUES ( 1, 'Jones', 30000, 10);
INSERT INTO employee VALUES ( 2, 'Hall', 35000, 10);
INSERT INTO employee VALUES ( 3, 'Kim', 40000, 10);
INSERT INTO employee VALUES ( 4, 'Lindsay', 38000, 10);
INSERT INTO employee VALUES ( 5, 'McKeough', 42000, 11);
INSERT INTO employee VALUES ( 6, 'Barnes', 41000, 11);
INSERT INTO employee VALUES ( 7, 'O''Neil', 36000, 12);
INSERT INTO employee VALUES ( 8, 'Smith', 34000, 12);
INSERT INTO employee VALUES ( 9, 'Shoeman', 33000, 12);
INSERT INTO employee VALUES (10, 'Monroe', 50000, 15);
INSERT INTO employee VALUES (11, 'Zander', 52000, 16);
INSERT INTO employee VALUES (12, 'Henry', 51000, 16);
INSERT INTO employee VALUES (13, 'Aaron', 54000, 15);
INSERT INTO employee VALUES (14, 'Scott', 53000, 16);
INSERT INTO employee VALUES (15, 'Mills', 70000, 17);
INSERT INTO employee VALUES (16, 'Goyal', 80000, 17);
INSERT INTO employee VALUES (17, 'Urbassek', 95000, NULL);
一、 基本操作
select empid,name,mgrid
from employee
start with name = 'Goyal' connect by prior empid=mgrid
CONNECT_BY_ROOT
二、 CONNECT_BY_ROOT 一元运算符（unary operator）
该一元运算符计算每个节点的根节点对应的表达式的值
用法：
CONNECT_BY_ROOT <表达式>
比较希望得到每个节点的根节点对应雇员的工资是否超过70000，如果是，则显示1
select empid,name,mgrid,abs(CONNECT_BY_ROOT salary-70000)/(CONNECT_BY_ROOT salary-70000)
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
或者当成函数来用
select empid,name,mgrid,abs(CONNECT_BY_ROOT(salary)-70000)/(CONNECT_BY_ROOT(salary)-70000)
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
三、 PRIOR 运算符
指明父子结点关联的条件
四、 LEVEL 伪列（pseudocolumn）
该伪列指明树查询中当前结点的树高
select empid,name,mgrid,level
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
五、 CONNECT_BY_ISCYCLE 伪列
该伪列指明该行的子结点是否也是该行的父结点。必须使用 NOCYCLE 关键字
select empid,name,mgrid,level,CONNECT_BY_ISCYCLE
from employee
start with name = 'Goyal' connect by NOCYCLE prior empid=mgrid;
例：
UPDATE employee SET mgrid = 5 WHERE empid = 17;
SELECT empid, name, mgrid,
CONNECT_BY_ISLEAF leaf, CONNECT_BY_ISCYCLE cycle
FROM employee
START WITH name = 'Goyal'
CONNECT BY NOCYCLE PRIOR empid = mgrid;
六、 CONNECT_BY_ISLEAF 伪列
该伪列标识该行在此次查询中是否还有子结点。
select empid,name,mgrid,level,CONNECT_BY_ISLEAF is_leaf
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
七、 SYS_CONNECT_BY_PATH 函数
返回一个字符串：表示根节点到当前节点的路径
select empid,name,mgrid,level,trim(SYS_CONNECT_BY_PATH(name,'/'))
from employee
start with name = 'Goyal' connect by prior empid=mgrid;
八、 SIBLINGS keyword in the ORDER BY clause
相当于深度优先遍历.
select empid,name,mgrid,level
from employee
start with name = 'Goyal' connect by prior empid=mgrid
ORDER SIBLINGS BY name asc;
(各兄弟之间按名字升序排列)