Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Explain in detail the basic principles of oracle paging query

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

This paper analyzes the basic knowledge of oracle paging query in detail from the principle of data query and the method of paging implementation. The following is the content of this paper:

Reason one

Oracle defaults to generating rowmun,rowid fields for each table, which we call pseudo columns.

1 create a test table

CREATE TABLE TEST (ID NUMBER,NAME VARCHAR2 (20))

2 insert test data

INSERT INTO TEST VALUES (1); INSERT INTO TEST VALUES (2); INSERT INTO TEST VALUES (3); INSERT INTO TEST VALUES (4); INSERT INTO TEST VALUES (5); INSERT INTO TEST VALUES (6); INSERT INTO TEST VALUES (7).

3 check the table field to confirm the field that comes with it

Select rowid,rownum,id,name from TEST

4 rowid is generally not needed. The internal Oracle is used to store the physical location of the line. What is related to paging is rownum, that is, the line number.

two

1 query rows less than 5, query four results

For the condition of select rowid,rownum,id,name from test where rownum 2 and rownum 2, the first row is removed, and the previous second row becomes the new first row. If this goes on, up to the last row, the condition can not be satisfied, so no data can be found.

3 correct writing: because > is not available, use the inner query to query the line number as a result set, and compare it with the inner result set in the outer layer.

Select rownum,id,name from (select rownum rn, u.* from test u where rownum2

4 if paging is carried out, such as three lines per page, if you want to query the second page, it is equivalent to looking up 6 pages, starting line 4 = (page number-1) * page length + 1, ending line 6 = page number * page length

Select rownum,id,name from (select rownum rn, t.* from test t where rownum = 4

5 similarly, you can change the query in 4 to the most common three-tier structure.

Select rownum,id,name from (select rownum rn, n.* from (select * from test-how to write the innermost loop) n where rownum = 4

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 261

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report