Typical paging code in Oracle

Typical paging code in Oracle

Since there is no top keyword in Oracle, the paging code in SQL Server does not apply to Oracle. How can paging be implemented in Oracle?

-- Query all data

STUNO STUNAME STUAGE STUID STUSEAAT
--------------------------------------------------------
9 Wang Wu 15 5.9876E + 15 5
13 Haha 15 5.9876E + 15 5
15 Li Si 12 1.5666E + 10 6
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5
1 66 10 5566554666 5

For example, I want to query the second to fourth records in the stuInfo table.

-- Two-layer nested Paging

SQL> -- two-layer nested Paging
SQL> select * from (select stuInfo. *, rownum as rn from stuInfo where rownum <= 4) where rn> = 2;

STUNO STUNAME STUAGE STUID STUSEAAT RN
------------------------------------------------------------------
13 Haha 15 5.9876E + 15 5
15 Li Si 12 1.5666E + 10 6
1 66 10 5566554666 5

-- If I do not have other sorting operations on the original table, two layers of nesting can meet the requirements, but if I have a condition that the student ID must be sorted in descending order first, what about the second to fourth records?

You must use layer-3 nesting.

SQL> select * from (select stu. *, rownum as rn from (select stuInfo. * from stuI
Nfo order by stuno desc) stu where rownum <= 4) where rn> = 2;

STUNO STUNAME STUAGE STUID STUSEAAT RN
------------------------------------------------------------------
15 Li Si 12 1.5666E + 10 6 2
13 Haha 15 5.9876E + 15 5 3
1 66 10 5566554666 5 4

This completes the paging query.