The Oracle PL/SQL LEVEL Keyword

最新推荐文章于 2025-12-04 07:03:54 发布

转载最新推荐文章于 2025-12-04 07:03:54 发布 · 533 阅读

文章标签：

#oracle #hierarchy #query #generator #structure #manager

SQL 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了Oracle PL/SQL中LEVEL伪列的概念及用法。LEVEL用于标识层级查询中的层级编号，对于根节点返回1，子节点依次递增。文章通过员工管理示例展示了如何使用LEVEL进行层级查询，并演示了其作为行生成器的功能。

Term: LEVEL

Definition:
In Oracle PL/SQL, the term LEVEL refers to a pseudo column in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format. For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. In a tree-like structure, the lowest level is the root (LEVEL=1) and it progresses in unit incremental ratio.

Note that LEVEL must be used in the CONNECT_BY queries.

Example Usage:

The SELECT query below lists employees reporting to a Manager:
   SELECT EMPNO, MGR_ID, LEVEL
   FROM EMPLOYEES E
   START WITH MGR_ID = NULL
   CONNECT BY PRIOR EMPNO = MGR_ID;

It can also be used as 'n' row generator using recursive hierarchy. The query below generates 50 sequence numbers.
   SELECT LEVEL SEQ
   FROM DUAL
   CONNECT BY LEVEL <= 50;