How to write paging in oracle

Oracle is a very popular relational database management system that provides rich SQL language functions, including OFFSET and FETCH statements for paging. In actual development, we often need to use paging to optimize query efficiency and improve user experience. This article will introduce how to implement paging function in Oracle.

1. Use ROWNUM for paging

Oracle has built-in ROWNUM pseudo-column, which will assign numerical numbers in sequence and can be used for paging operations. We can get the entire result set through query, and then use ROWNUM to filter out the data with the specified page number and record number.

For example, suppose we have a table named EMPLOYEE and need to query page 2 and display 10 records on each page. You can use the following SQL statement to implement paging.

SELECT *
FROM (
  SELECT ROWNUM rn, e.*
  FROM EMPLOYEE e
  WHERE ROWNUM <= :page * :pageSize
)
WHERE rn > (:page - 1) * :pageSize

Among them, :page and :pageSize are parameters, indicating the page number to be queried and the number of records displayed on each page. The specific process of this SQL statement is as follows:

1. The internal query first sorts the records in the EMPLOYEE table according to ROWNUM size, and assigns a ROWNUM number to each record.
2. The external query then operates on the result set of the internal query, using WHERE rn > (:page - 1) * :pageSize to filter out the data with the specified page number and record number.

Since ROWNUM is allocated after sorting, the internal query must sort the data first, otherwise the paging results will be uncertain.

It should be noted that if the query result only has one record, using the above SQL statement may cause the result set to be empty. The solution is to change WHERE rn > (:page - 1) :pageSize to WHERE rn BETWEEN ((:page - 1) :pageSize 1) AND (:page * :pageSize).

2. Use OFFSET and FETCH paging

Starting from Oracle 12c, it supports paging using OFFSET and FETCH statements. This method does not require the use of ROWNUM, and the syntax is more concise and clear. The following is an example of using OFFSET and FETCH to implement paging:

SELECT *
FROM EMPLOYEE
ORDER BY emp_id
OFFSET (:page - 1) * :pageSize ROWS
FETCH NEXT :pageSize ROWS ONLY

Among them, :page and :pageSize respectively represent the page number to be queried and the number of records displayed on each page. The specific process of this SQL statement is as follows:

1. First sort the records in the EMPLOYEE table according to emp_id.
2. OFFSET (:page - 1) * :pageSize ROWS specifies the number of rows to skip, that is, the position of the starting record to be queried in the sorted table.
3. FETCH NEXT :pageSize ROWS ONLY specifies the number of records to be queried, that is, the number of records displayed on each page.

Be careful when using OFFSET and FETCH statements to query. If the number of records queried is less than one page, the query result will be empty. In this case, we need to manually calculate the number of remaining records and filter using WHERE clause.

3. Summary

The above introduces two methods to implement paging function in Oracle database: using ROWNUM and using OFFSET and FETCH. Both methods have their own advantages and disadvantages, and developers can choose the appropriate method according to specific application scenarios.

It should be noted that in order to improve query efficiency, appropriate indexes should be used when paging. At the same time, if the query results exceed 1,000, the paging query should be converted into a batch query to avoid returning too much data in a single query.

The above is the detailed content of How to write paging in oracle. For more information, please follow other related articles on the PHP Chinese website!