How to paginate in oracle

Oracle is a widely used database management system that fully supports the SQL language standard. In many scenarios, we need to display query results in pages to facilitate data management and viewing. Next, this article will introduce how Oracle performs paging operations.

1. Use ROWNUM for paging

Oracle provides a ROWNUM function, which can identify each row in the returned result set as a unique value, and the value is numbered starting from 1. self-increasing. This function has the following characteristics:

1. The return result contains the ROWNUM column, which is used to record the row number of the data row.
2. The calculation rule of ROWNUM is to start counting when the first N rows of data that meet the conditions are returned after the user-specified WHERE condition and ORDER BY sorting.
3. Can only perform one value check and return qualified record rows, and cannot be used for continuous query paging operations.

Then, we can use the ROWNUM function to perform data paging operations. The specific implementation steps are as follows:

1. Write a query SQL statement and specify the data result set that needs to be paged.
2. Use the ROWNUM function based on the query result set, and specify the number of rows to be queried and the starting number of rows.

For example:

SELECT FROM (SELECT A., ROWNUM RN FROM (SELECT * FROM user_data ORDER BY user_id) A WHERE ROWNUM <= 20) WHERE RN >= 1;

In the above SQL statement, a subquery statement is used to query all data from the user_data table and sort it according to the user_id field. Finally, use the ROWNUM function based on the query result set and limit the number of rows returned in the query result set to 20 rows. The result of using the above SQL statement is the data records from row 1 to row 20.

2. Use OFFSET...FETCH for paging

In view of the limitations of ROWNUM paging method, Oracle has provided a new paging method since version 12c, namely OFFSET FETCH interpolation syntax . Compared with the ROWNUM function, the characteristics of the OFFSET FETCH syntax are:

1. does not depend on the line number, but the processing sequence value specified by ON (OFFSET refers to the offset) and the LIMIT value ( FETCH refers to the number of rows fetched) to obtain the result set.
2. Can continuously query paging data, suitable for large amounts of data.
3. Query results using OFFSET FETCH syntax will not be affected by WHERE and ORDER BY statements.

The specific usage method is as follows:

SELECT * FROM user_data ORDER BY user_id OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

In the above SQL statement, OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY syntax indicates that the specified offset is row 0 and 20 rows of data records are obtained.

Summary:

Oracle provides two paging data query methods: ROWNUM function and OFFSET FETCH syntax. The ROWNUM function is suitable for situations where the amount of data is small and the paging operation only needs to be performed once. The OFFSET FETCH syntax is suitable for situations where the amount of data is large and multiple paging needs to be queried continuously.

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