Quick Search:
 
 The Oracle PL/SQL LEVEL Keyword      [Return To Index] Jump to:  

Term: LEVEL

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:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org