The term LEVEL refers to a Pseudocolumn in Oracle which is used in a hierarchical query to identify the hierarchy level (parent->child) in numeric format. The LEVEL returns 1 for root row, 2 for child of root row and so on, as tree like structure. LEVEL must be used with CONNECT BY Clause. The CONNECT BY clause defines the hierarchical relationship between the parent rows and the child rows of the hierarchy. DUAL is a dummy table automatically generated by Oracle database along with data dictionary.
Above query will execute Level having initial value 1 from dual, which is dummy table. ‘Sequence’ act as an ALias Name i.e., Temporary name of column. In query, condition will be checked and relationship created using Connect By between Level having value 1 and the specified condition. It will display values till it pass that specified condition.
Start Sequence from specific number:
Level can be helpful to start a sequence of number from any particular initial value. Only need to add up one less than that value to Level with having condition in Connect By.
Where n is initial specific number and Alias Name is temporary defined name of column.
For display of sequence of numbers from 6 to 10. In above example, add up one less than 6 to Level and having condition till 10 using Connect By. It will execute and display range specified value as column name ‘Sequence’ from dummy table Dual.
Above query will execute and display both Sequence of Numbers as well as Date. Level have Temporary column name Sequence and Date have column name System_date, as defined. It will retrieve data from dummy table called Dual. It will have 5 numbers of rows according to the condition specified in Connect By, from 1 to 5 in Sequence column and have Same date in all 5 rows in System_date column.
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Difference between Row level and Statement level triggers
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between SQL and T-SQL
- Sequence with examples in SQL Server
- Swap two numbers in PL/SQL without using temp
- Print all even numbers from 1 to n in PL/SQL
- Swap two numbers in PL/SQL
- Sum and average of three numbers in PL/SQL
- Sum Of Two Numbers in PL/SQL
- Print all odd numbers and their sum from 1 to n in PL/SQL
- Greatest number among three given numbers in PL/SQL
- Convert the given numbers into words in Pl/SQL
- GCD of two numbers in PL/SQL
- Finding sum of first n natural numbers in PL/SQL
- Print all the prime numbers between 'm' and 'n' in PL/SQL
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL using Python | Set 1
- SQL | USING Clause
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.