How to paginate oracle database

Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.

Oracle Database Paging Mechanism

In Oracle databases, paging can be implemented using ROWNUM pseudo-columns or FETCH statements.

ROWNUM pseudo column

grammar:

 <code>SELECT * FROM table_name WHERE ROWNUM  * <page_number></page_number></code>

illustrate:

• ROWNUM is a pseudo column that indicates the row number of the current row.
• page_size is the number of rows per page.
• page_number is the number of pages to be retrieved.

For example:

Get page 2, 5 rows of data per page:

 <code>SELECT * FROM table_name WHERE ROWNUM </code>

FETCH statement

grammar:

 <code>FETCH FIRST <page_size> ROWS ONLY FROM table_name</page_size></code>

illustrate:

• The FETCH statement returns the first row of the specified number.
• page_size is the number of rows per page.

For example:

Get the second page, 5 rows of data per page:

 <code>FETCH FIRST 5 ROWS ONLY FROM table_name OFFSET 5 ROWS</code>

Select a paging method

• If complex filtering or sorting is required in the query, use the ROWNUM pseudo-column.
• If you only need to get the specified number of first rows (no complicated filtering or sorting) then use the FETCH statement.

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