0


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

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

在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

CREATETABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER PRIMARYKEY,
    EMPLOYEE_NAME VARCHAR2(50),
    MANAGER_ID NUMBER
);INSERTINTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID)VALUES(1,'张三',NULL);INSERTINTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID)VALUES(2,'李四',1);INSERTINTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID)VALUES(3,'王五',1);INSERTINTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID)VALUES(4,'赵六',2);INSERTINTO EMPLOYEES (EMPLOYEE_ID, EMPLOYEE_NAME, MANAGER_ID)VALUES(5,'孙七',2);INSERTINTO 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
STARTWITH MANAGER_ID ISNULLCONNECTBY 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",LEVELFROM EMPLOYEES
STARTWITH MANAGER_ID ISNULLCONNECTBY PRIOR EMPLOYEE_ID = MANAGER_ID;

输出结果,

在这里插入图片描述

在这个查询中,

LPAD

函数用于在员工名字前添加空格,以视觉上表示层级关系。

LEVEL

伪列表示每个员工在层次结构中的层级。

3.3. 查询特定员工的子级

如果我们只想查询某个特定员工的所有下属,可以使用

START WITH

来指定这个员工。

SELECT LPAD(' ',2*(LEVEL-1))|| EMPLOYEE_NAME AS"Employee Name",LEVELFROM EMPLOYEES
STARTWITH EMPLOYEE_NAME ='李四'CONNECTBY PRIOR EMPLOYEE_ID = MANAGER_ID;

输出结果,

在这里插入图片描述

这个查询将从名为“张三”的员工开始,然后列出他的所有下属,包括间接下属。

4. 结论
CONNECT BY

PRIOR

START WITH

是Oracle数据库中处理层次结构数据的强大工具。通过这些关键字,你可以轻松地查询和展示复杂的组织结构或分类目录。对于初级Oracle程序员来说,掌握这些关键字的用法将极大地提升你的数据库查询能力。

标签: oracle 数据库

本文转载自: https://blog.csdn.net/engchina/article/details/143191303
版权归原作者 engchina 所有, 如有侵权,请联系我们删除。

“Oracle CONNECT BY、PRIOR和START WITH关键字详解”的评论:

还没有评论