What is the usage of the oracle limit?

The LIMIT clause in Oracle is used to restrict the number of rows retrieved from the database. In Oracle, the LIMIT clause is known as ROWNUM, and it can be used in conjunction with the SELECT statement.

The general syntax of a LIMIT clause is as follows:

Select column_name from table_name where condition and rownum is less than or equal to n.

“其中，n表示要搜索的行数。”

It’s worth noting that the LIMIT clause in Oracle differs from that of other database systems like MySQL. In Oracle, the LIMIT clause must be used in conjunction with the WHERE clause and must appear after the WHERE clause. Additionally, in Oracle, the LIMIT clause restricts the number of rows returned, rather than the number of rows skipped.

To skip a certain number of rows and retrieve the next set of rows, you can achieve this using a subquery. For example:

Select all columns from a table where the condition is met within a specified range of rows in a subquery.

In which, m represents the number of lines to be skipped, while n represents the number of lines to be retrieved.

In summary, Oracle’s LIMIT clause can be used to restrict the number of rows retrieved from the database, but it differs from the LIMIT statement in other database systems. It must be used with a WHERE clause and can utilize subqueries to skip a certain number of rows and fetch the following rows.