Paging Oracle Paging for three common databases (Oracle, MySQL, and SQLServer)

Oracle paging solution for Environment Oracle11gR2SQLPlus -- create the test table SQLcreatetabletest2 (3 idnumberprimarykey, 4namevarchar2 (20) notnull5); the table has been created. -- Create the sequence SQLSQLcreatesequenceseq_wgb_test; the sequence has been created. -- Insert SQLinsertintot

Oracle 11gR2 SQLPlus environment Oracle paging solution-create a test table SQL create table test 2 (3 id number primary key, 4 name varchar2 (20) not null 5); the table has been created. -- Create a sequence SQLSQL create sequence seq_wgb_test; the sequence has been created. -- Insert data SQL insert into t

Environment

Oracle 11gR2 + SQLPlus

Problem

Oracle Paging

Solution

-- Create test table SQL> create table test 2 (3 id number primary key, 4 name varchar2 (20) not null 5); the table has been created. -- Create a sequence SQL> create sequence seq_wgb_test; the sequence has been created. -- Insert data SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test1'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test2'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test3'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test4'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test5'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test6'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test7'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test8'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test9'); 1 row has been created. SQL> insert into test (id, name) values (seq_wgb_test.nextval, 'test10'); 1 row has been created. SQL> insert into test (id, name) select seq_wgb_test.nextval, name from test; 10 rows have been created. SQL> insert into test (id, name) select seq_wgb_test.nextval, name from test; 20 rows have been created. SQL> insert into test (id, name) select seq_wgb_test.nextval, name from test; 40 rows have been created. -- Execute paging SQL> select t. * 2 from 3 (4 select rownum sn, te. * 5 from test te 6 where rownum <= 10 7) t 8 where t. sn> 0; sn id name ---------- ------------------ 1 2 test1 2 3 test2 3 4 test3 4 5 test4 5 6 test5 6 7 test6 7 8 test7 8 8 9 test8 9 10 test9 10 11 test10 selected 10 rows.

Running Effect

Tips

Fast data insertion:

Insert into test (id, name) select seq_wgb_test.nextval, name from test;

Data Replication in Oracle is inconsistent with that in MySQL and SQLServer. Note that the auto-increment mode is different.

Summary syntax

In Oracle, subqueries and rownum are used for paging.

Select t .*

From

(

Select rownum sn, te .*

From tableName te

Where rownum <= num * page

) T

Where t. sn> num * (page-1 );

-- Num: number of lines displayed per page

-- Page: page number

The page corresponding to the Web program is similar:

Select t .*

From

(

Select rownum sn, te .*

From tableName te

Where rownum <= pageSize * pageNow

) T

Where t. sn> num * (pageNow-1 );

-- PageNow: Current page

-- PageSize: number of records displayed on each page

References

Http://blog.csdn.net/wentasy/article/details/8200512

Http://blog.csdn.net/wentasy/article/details/8200561