CONNECT BY子句

CONNECT BY子句具有以下常规形式：

CONNECT BY { PRIOR parent_expr = child_expr |
 child_expr = PRIOR parent_expr }

在候选父行上对 parent_expr 求值。如果 FROM 所返回行的 parent_expr = child_expr 结果为 TRUE，则此行被视为父行的子行。

以下可选子句可以与 CONNECT BY 子句一起指定：

START WITH start_expression

由 FROM 子句（start_expression 的求值结果为 TRUE）返回的行将成为层次结构的根节点。

ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...]

层次结构的同级行按结果集中的 expression 排序。

准备测试数据

创建一张名为employees的表并插入数据用于测试，示例如下：

-- 创建employees表
CREATE TABLE employees (
  employee_id NUMBER(11) NOT NULL,
  last_name VARCHAR2(16) NOT NULL,
  manager_id NUMBER(11),
  department_id NUMBER(4) NOT NULL,
  CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

-- 插入示例数据
INSERT INTO employees (employee_id, last_name, manager_id, department_id) VALUES
(100, 'King', NULL, 80),  -- 员工100是部门80的经理
(101, 'Blake', 100, 80),  -- 员工101是员工100的直接下属
(102, 'Clark', 100, 80),  -- 员工102是员工100的直接下属
(103, 'Miller', 101, 80), -- 员工103是员工101的直接下属
(200, 'Kochhar', NULL, 110),  -- 员工200是部门110的经理
(201, 'Higgins', 200, 110),  -- 员工201是员工200的直接下属
(202, 'Gietz', 200, 110),  -- 员工202是员工200的直接下属
(203, 'Sciarra', 201, 110); -- 员工203是员工201的直接下属

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAF能够判断当前行是否为叶子节点，是则返回1或true，否则返回0或false。

CONNECT_BY_ISLEAF语句示例如下：

SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL <= 3 AND department_id = 80
   START WITH employee_id = 100
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

返回示例如下：

 Employee | IsLeaf | level |        Path        
----------+--------+-------+--------------------
 King     |      0 |     1 | /King
 Blake    |      0 |     2 | /King/Blake
 Miller   |      1 |     3 | /King/Blake/Miller
 Clark    |      1 |     2 | /King/Clark
(4 rows)

CONNECT_BY_ROOT

CONNECT_BY_ROOT是一元运算符，仅在分层查询中有效。 使用此运算符限定一列时，使用根行中的数据返回列值。 该运算符不仅返回直接父行，而且还返回层次结构中的所有祖先行，从而扩展了层次结构查询的CONNECT BY [PRIOR]条件的功能。

CONNECT_BY_ROOT语句示例如下：

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           
----------+---------+---------+--------------------------
 Gietz    | Kochhar |       1 | /Kochhar/Gietz
 Higgins  | Kochhar |       1 | /Kochhar/Higgins
 Sciarra  | Kochhar |       2 | /Kochhar/Higgins/Sciarra
 Sciarra  | Higgins |       1 | /Higgins/Sciarra
(4 rows)