PL/SQL ROWNUM

Introduction to PL/SQL ROWNUM

PL/SQL provides different types of functions to the user, in which ROWNUM() is one of the functions provided by the PLSQL. Basically, ROWNUM() function is used to return the number in which that oracle selects the rows from the specified table or joined table that is dependent on the user requirement. After the execution of the ROWNUM() function, it returns the rows in a sequential manner, such as 1, 2, and so on. We can perform the different operations by using the ROWNUM() function that we can use with an order by clause and Limit clause.

Syntax:

select ROWNUM, colm name from specified table name <specified expression order by column name;

Explanation

In the above syntax, we used the ROWNUM() function with different parameters as follows.

First, we use a select clause with ROWNUM() Function as shown in the above syntax.

• colm name: Colm name means actual column name from the specified table name.
• specified table name: Specified table means actual table names that are created by the user.
• <specified expression: This parameter is optional in this syntax; if required, then we can specify the expression.
• order by column name: At the end of syntax, we used to order by column name; this is also an optional part of this syntax.

How ROWNUM works in PL/SQL?

Now let’s see how the ROWNUM() function works in PL/SQL as follows. In a query, ROWNUM is a pseudo column (not an actual column) that is accessible. The numbers 1, 2, 3, 4,…. will be allocated to ROWNUM. ROWNUM is used with a set of N rows, where N is the number of rows in the set. Basically, the ROWNUM() function does not assign the permanent values to the rows, and during the execution, it does not ask any number to display the result.
Many individuals are also perplexed as to when a ROWNUM value is really assigned. After a row passes the predicate step of the query, but before any sorting or aggregation, a ROWNUM value is issued to it. Furthermore, a ROWNUM number is only incremented after it is allocated, so then why does the following query not return a row as follows.

select * from specified table name where ROWNUM >1;

Explanation

In the above statement, we write the ROWNUM is greater than 1, and this condition is not true for the first row. The ROWNUM() function does not directly show the second row from the table. As a result, no ROWNUM value may ever be larger than 1. Consider the following query structure.

Select…., ROWNUM
From specified table name
Where <Condition>
Group by<Specified Column Name>
Having <condition for having clause>
Order by <specified column name>

The above statement will be executed in the following order as follows.

1. It first executes the either from or where clause.
2. After that ROWNUM() function assigned the number in increment order.
3. Now applied the select clause.
4. After selection, the clause group by clause is applied.
5. After group by clause, a having clause is applied.
6. Finally, Order by clause is applied.

If ROWNUM is followed by an ORDER BY clause in the same query, the rows will be reordered by the ORDER BY clause. The outcomes may differ based on how the rows are accessed. For example, if Oracle uses an index to access the data because of the ORDER BY clause, Oracle may get the rows in a different order than if the index wasn’t used. As a result, the following sentence will have a different result from the prior example.

Select * from specified table name where ROWNUM < expression order by Column name;

If you embed the ORDER BY clause in a subquery, you may force the ROWNUM condition to be applied after the ORDER BY clause in the subquery and the ROWNUM condition in the top-level query.

Examples

Now let’s see different examples of the ROWNUM() function in PL/SQL for better understanding as follows.

First, we need to create the table by using the following create table statement as follows.

create table students(stud_id number(10) not null, stud_name varchar2(30) not null, stud_city varchar2(30));

Explanation

In the above example, we use the create table statement to create a new table name as students with different attributes such as stud_id, stud_name, and stud_city with different data types, as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

Now insert some records to perform the ROWNUM() function as follows.

insert into students(stud_id, stud_name, stud_city) values(101,'Jenny','Mumbai');
insert into students(stud_id, stud_name, stud_city) values(102,'Johan','Mumbai');
insert into students(stud_id, stud_name, stud_city) values(103,'Pooja','London');
insert into students(stud_id, stud_name, stud_city) values(104,'Sameer','London');
insert into students(stud_id, stud_name, stud_city) values(105,'Rohit', 'London');

Explanation

By using insert into the statement, we inserted 5 different records as shown in the above statement. The final output of the above statement we illustrated by using the following screenshot as follows.

Now everything is ready to perform the ROWNUM() function as follows.

select ROWNUM, students.* from students where stud_id>101;

Explanation

In the above example, we try to implement the ROWNUM() function; here, first, we use the select clause with ROWNUM() to get the number in a sequential manner from the student’s table. After that, we specify the condition by using the where clause; in this example, we need to display rows from whose stud_id is greater than 101, as shown. The final output of the above statement we illustrated by using the following screenshot as follows.

Now let’s see how we can use order by clause with ROWNUM() function as follows.

select ROWNUM, students.* from students where stud_id>101 order by stud_name;

Explanation

The final output of the above statement we illustrated by using the following screenshot as follows.

select * from(select students.* from students where stud_id>101 order by st ud_name) where ROWNUM<3;

Explanation

In the above example, we try to implement a sub-query with the ROWNUM() function. The final output of the above statement we illustrated by using the following screenshot as follows.

Now let’s see how we can use subquery with an order by clause, either ASC or DESC order.

select * from(select students.* from students where stud_id>101 order by st ud_name desc) where ROWNUM<3;

Explanation

The final output of the above statement we illustrated by using the following screenshot as follows.

Conclusion

We hope from this article you learn PL/SQL ROWNUM. From the above article, we have learned the basic syntax of ROWNUM, and we also see different examples of the ROWNUM. From this article, we learned how and when we use PL/SQL ROWNUM.

Recommended Articles

This is a guide to PL/SQL ROWNUM. Here we discuss the definition, How ROWNUM works in PL/SQL? along with examples, respectively. You may also have a look at the following articles to learn more –

1. Careers in PL/SQL
2. PL/SQL TRIM
3. PL/SQL Date Functions
4. PL/SQL Collections