What is the usage of rownum in oracle

In Oracle, rownum is a virtual column introduced by Oracle. It is only constructed when querying. It is used to limit the total number of rows returned by the query and assign the number of rows returned from the query. The number always starts from 1. By analogy, rownum cannot be prefixed with the name of any table.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of rownum in Oracle

For rownum, it is the number that the Oracle system sequentially assigns to the rows returned from the query. The first row returned is assigned 1, and the second Rows are 2, and so on. This pseudo field can be used to limit the total number of rows returned by the query, and rownum cannot be prefixed with the name of any table.

rownum is a virtual column introduced by Oracle.
This virtual column does not exist physically, it is only constructed during query. Pseudo columns are usually freely allocated and cannot be modified by users.

Features

There are the following main features about rownum:

1) rownum does not belong to any table.

2) The premise for the existence of rownum is the result table first.

3) rownum always starts from 1.

4) rownum is generally only used with

5) Using rownum for paging query requires converting rownum into a real column and querying against rownum.

2. Mechanism principle

The usage of rownum may seem strange, but in fact it will be very simple if you understand its mechanism principle.

First we do a simple experiment: take out all the fields from the dual table and take out the rownum.

If we use the following writing method: t.rownum

When running in this way, a 01747 error will be reported:

Because in fact, the rownum field does not exist in the dual table, so we cannot use the t.rownum format.

The correct way to write it should be:

So, rownum is a virtual column and does not belong to any table.

So where did this virtual column come from. We are doing a simple experiment for easy understanding:

As follows, we have a simple table: test_ljb, with a total of ten records.

We add rownum.

The results are as follows, easy to understand, select ten records, rownum is from 1 to 10

We add a salary filter condition:

The results are as follows: three records are selected, rownum is from 1 to 3

It should be noted that the second result table The employee corresponding to the rownum does not correspond to the first one.

For example: when the rownum of the first table is 1, it corresponds to Arvin, and the second table corresponds to Oracle.

The reasons are as follows:

Because rownum is a pseudo column added to the result set, that is, a column that is added after the result set is found first.

Simply put, rownum is the sequence number of the results that meet the conditions. It always starts from 1, so it is impossible for the selected result to skip 1 and have other values ​​greater than 1.

In other words, rownum is dynamic and changes in real time according to the new result set.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What is the usage of rownum in oracle. For more information, please follow other related articles on the PHP Chinese website!