Detailed explanation of the basic principles of Oracle paging query

This article mainly introduces the principle of Oracle paging query in detail, and analyzes the implementation method from the example test data. This article analyzes the basic knowledge of Oracle paging query in detail from the data query principle and paging implementation method. The following is this article Content:

Reason 1

Oracle generates rowmun and rowid fields for each table by default. We call these fields 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 fields and confirm that the built-in fields

select rowid,rownum,id,name from TEST;

4 rowid are generally not used. , used internally by Oracle to store the physical location of rows. Related to paging is rownum, which is the row number

二

1 Query rows less than 5 and get four results

select rowid,rownum,id,name from test where rownum 2facd05c9ed05f06320bbd66af137ead2 and rownum 012445190654a106e44b338148384338 cannot be used, use the inner query to query the row number as a result set, and use the inner result set for comparison in the outer layer. <p class="cnblogs_code"><br></p><pre class="brush:php;toolbar:false">select rownum,id,name from
( select rownum rn, u.* from test u where rownumf6456af7e9d45973fb65c284a41d2e8b2

4 If paging is performed, for example, there are three rows per page, you need to query the second page, it is equivalent to checking 4, 5, 6 items, starting line 4 = (page number-1) * length of each page + 1, ending line 6 = page number * length of each page

select rownum,id,name from (
  select rownum rn , t.* from test t where rownum a15c685b42299c7d65c5378e1cf9c539=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 --最内层循环该怎么写怎么写
    ) n where rownum <=6 --小于限制写在第二层
  ) 
 where rn>=4

The above content is the basic principle of Oracle paging query, I hope it can help everyone.

Related recommendations:

Oracle paging function example implemented in PHP

##php+oracle paging class_PHP tutorial

oracle paging query sql principles and statements

The above is the detailed content of Detailed explanation of the basic principles of Oracle paging query. For more information, please follow other related articles on the PHP Chinese website!