Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL LEVEL Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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 = 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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 66 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?