Search by page in Oracle Database

In actual work, we often encounter a problem when building a custom search engine, that is, implementing some type of paging function. That is to say, you can submit a query that returns many rows of data, but only the first 20 rows are displayed. When a user clicks a link, the next 20 or the first 20 pieces of data will be taken out of the database application.
A problem with database access is that requests from the Web site are stateless. When a user requests the next set of data, it is very inefficient to maintain a cursor in the database. You can write code for HTML so that the cursor can be placed on the client, but the cursor is a limited resource, so it is best to close the cursor every time the page load is complete.
Oracle's cursor does not support moving backward through the rowset, but the user will always go back in the browser or not request a row set in order. Obviously, it is the responsibility of the database server to return finite rows of data.
The pseudo column rownum contains the current row number. Many people will find that the following method is invalid when they first try to return a sub-set of records in the middle of the table:
Select * From all_objects where rownum between 30 and 49;

This method does not work because the rownum column is applied to the record only when the record is retrieved or filtered. The first row is thrown when it is retrieved because its rownum is 1. Then, the next row is taken out; it will also be thrown because it is the new "1", and so on until all rows are used. This query does not return any records. The solution is to retrieve 1 to 30 rows of records when you see records between 30 and 50:
Select * From all_objects where rownum <= 49;

Then, you can use it as a subquery and filter out all records before the start point (note that I must provide an alias for "rownum" to compile ):

Select * from (select rownumr, all_objects. * From all_objects where rownum <=
49) T
Where T. R> = 30;

To ensure its efficiency, we can use the binding variable for this restriction. This will make all such requests literally identical, thus eliminating re-parsing the query when each request has different ranges:

Select * from (select rownumr, all_objects. * From all_objects where rownum <=
: Min) T
Where T. R >=: Max;

If the language you use can return the row set through the stored procedure, the database automatically processes the bound variable internally. Then, the application code will only receive records of its requests, instead of deciding which records to receive.

Create or replace procedure search
(P_mininteger, p_maxinteger, p_rowset out
Sys_refcursor)
Is
Begin
Select cursor (*) into p_rowset
From (select rownumr, all_objects. * From all_objects where rownum <=
P_max) T
Where T. R> = p_min;
End search;
/
Show errors

Note: You must note that rownum is calculated after sorting. Therefore, use the order by clause to obtain a new record order. However, the rule-based optimizer uses rownum to perform a "short-circuit" query. When the rownum clause is satisfied, it returns a record to the next part of the query.