Oracle CONNECT BY、PRIOR和START WITH关键字详解

- - - 1. 基本概念
    - 2. 数据示例
    - 3. SQL示例
    - - 3.1. 查询所有员工及其上级
      - 3.2. 显示层次结构
      - 3.3. 查询特定员工的子级
    - 4. 结论

在Oracle数据库中，CONNECT BY、PRIOR和START WITH关键字主要用于处理层次结构数据，例如组织结构、分类目录等。这些关键字通常与SELECT语句一起使用，以生成层次结构的查询结果。对于初级Oracle程序员来说，理解这些关键字的用法是非常重要的，因为它们可以帮助你更有效地处理和展示层次数据。

1. 基本概念

CONNECT BY: 这个关键字用于指定层次查询的连接条件，即如何从一个节点找到它的子节点。
PRIOR: 这个关键字用于指定层次查询中的父节点。通常与CONNECT BY一起使用。
START WITH: 这个关键字用于指定层次查询的根节点，即查询的起点。

2. 数据示例

假设我们有一个简单的员工表EMPLOYEES，其中包含员工ID (EMPLOYEE_ID)、员工姓名 (EMPLOYEE_NAME) 和上级ID (MANAGER_ID)。上级ID指向该员工的直接上级。如果一个员工没有上级，那么MANAGER_ID为NULL。

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARY KEY,
    EMPLOYEE_NAME VARCHAR2(50),
    MANAGER_ID NUMBER
);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (1, '张三', NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (2, '李四', 1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (3, '王五', 1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (4, '赵六', 2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (5, '孙七', 2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID) VALUES (6, '周八', 3);

COMMIT;

3. SQL示例

3.1. 查询所有员工及其上级

我们可以使用CONNECT BY和PRIOR关键字来查询所有员工及其上级。这里，我们使用PRIOR来指定上级员工的ID。

SELECT EMPLOYEE_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

输出结果，

在这里插入图片描述

这个查询从没有上级的员工（即MANAGER_ID IS NULL）开始，然后通过CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID来连接每个员工与其上级。结果将显示所有员工及其上级。

3.2. 显示层次结构

为了更清晰地显示层次结构，我们可以使用LEVEL伪列来表示每个员工的层级。

SELECT LPAD(' ', 2*(LEVEL-1)) || EMPLOYEE_NAME AS "Employee Name", LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

输出结果，

在这里插入图片描述

在这个查询中，LPAD函数用于在员工名字前添加空格，以视觉上表示层级关系。LEVEL伪列表示每个员工在层次结构中的层级。

3.3. 查询特定员工的子级

如果我们只想查询某个特定员工的所有下属，可以使用START WITH来指定这个员工。

SELECT LPAD(' ', 2*(LEVEL-1)) || EMPLOYEE_NAME AS "Employee Name", LEVEL
FROM EMPLOYEES
START WITH EMPLOYEE_NAME = '李四'
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;