Paging query sql statement Oracle

Paging query is a technology commonly used in database queries. It can realize the function of displaying data by page. Oracle is a powerful relational database management system, and it also has its own set of unique syntax and functions when implementing paging queries. This article will introduce in detail how to use SQL statements to implement paging queries in Oracle.

1. Common syntax for implementing paging queries in Oracle

The syntax for implementing paging queries in Oracle is ROWNUM and subquery, that is, ROWNUM is used to limit the number of rows returned, and subqueries are used to control the query at the same time. start line and end line.

The syntax format is as follows:

SELECT * FROM (
SELECT t.*, ROWNUM RN
FROM (SELECT * FROM table_name ORDER BY column_name ASC) t
WHERE ROWNUM <= :page * :size
)
WHERE RN > (:page - 1) * :size;

It should be noted that: page and :size respectively represent the page. number and amount of data per page.

2. Specific steps to implement paging query in Oracle

1. Create a table for paging query demonstration

We need to create a test table for Implement paging queries in Oracle. The following is the SQL statement:

CREATE TABLE t_page_query
(
id NUMBER(10) PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
age NUMBER(3) NOT NULL
);

2. Insert test data

We continue to insert data, the following is the SQL statement:

INSERT INTO t_page_query(id, name, age) VALUES( 1, 'Tom', 18);
INSERT INTO t_page_query(id, name, age) VALUES(2, 'Jack', 20);
INSERT INTO t_page_query(id, name, age) VALUES(3, 'Lucy', 19);
INSERT INTO t_page_query(id, name, age) VALUES(4, 'Amy', 22);
INSERT INTO t_page_query(id, name, age) VALUES(5, 'Jessie ', 21);
INSERT INTO t_page_query(id, name, age) VALUES(6, 'Chris', 23);
INSERT INTO t_page_query(id, name, age) VALUES(7, 'Kris', 18);
INSERT INTO t_page_query(id, name, age) VALUES(8, 'Nick', 19);
INSERT INTO t_page_query(id, name, age) VALUES(9, 'Jim', 25) ;
INSERT INTO t_page_query(id, name, age) VALUES(10, 'Lisa', 26);
INSERT INTO t_page_query(id, name, age) VALUES(11, 'Anthony', 22);
COMMIT;

3. Paging query demonstration

We use the t_page_query table created above to implement the paging query demonstration. The following is the SQL statement:

SELECT * FROM (
SELECT t.*, ROWNUM RN
FROM (
SELECT *
FROM t_page_query
ORDER BY age ASC
) t
WHERE ROWNUM <= :page * :size
)
WHERE RN > (:page - 1) * :size;

:page and :size in the above SQL statement are used to pass in the number of pages and the amount of data displayed on each page The parameters can be dynamically passed in through the programming language.

Paging query is used in many scenarios. For example, when displaying a large amount of data on a website, in order to save bandwidth, the data is usually loaded in pages. Through the introduction of this article, we believe that you have learned how to use SQL statements to implement paging queries in Oracle.

The above is the detailed content of Paging query sql statement Oracle. For more information, please follow other related articles on the PHP Chinese website!