Definition:
In Oracle PL/SQL, the term LEVEL refers to a pseudocolumn 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 IS 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;
Related Links:
Related Code Snippets: