Oracle分页写法大全【博森瑞】

Oracle分页写法大全

我们在逛一些网站时经常会遇到这种情况,由于返回的数据太多无法在一个页面中完全展示.需要在多个页面中显示完全.界面类似如下：

注意页面的右下角1/33 告诉我们商品一共有33页.

那么这种需求在SQL语句中如何实现?这就是我们经典的分页问题.

其实在这方面Oracle分页写法的复杂程度要高于MySQL分页写法.

不过在12C中推出的新特性,写完与MySQL已经一模一样,只是语法稍有不同.

在这里我们来总结下,Oracle实现分页查询的几种写法.

--经典rownum写法

dao@>create table dao_objectsas select * from dba_objects ;

Table created.

--选择对象编号最大的前五行

dao@>select rownumrn, object_id, object_name

2 from (select object_id, object_name

3 from dao_objects d

4 order by object_iddesc)

5 where rownum<= 5

RN OBJECT_ID OBJECT_NAME

---------- ---------- --------------------------------------------------------------------------------------------------------------------------------

1 92015 DAO_OBJECTS

2 92005 WRM$_DEEP_PURGE_INTERVAL

3 92003 WRI$_OPTSTAT_HISTGRM_HISTORY

4 92002 SYS_IL0000012928C00016$$

5 92001 SYS_LOB0000012928C00016$$

Execution Plan

----------------------------------------------------------

Plan hash value: 4183165338

-----------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 395 | | 1168 (1)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | | |

| 2 | VIEW | | 90914 | 7013K| | 1168 (1)| 00:00:01 |

|* 3 | SORT ORDER BY STOPKEY| | 90914 | 2663K| 3576K| 1168 (1)| 00:00:01 |

| 4 | TABLE ACCESS FULL | DAO_OBJECTS | 90914 | 2663K| | 426 (1)| 00:00:01 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<=5)

3 - filter(ROWNUM<=5)

Statistics

----------------------------------------------------------

2 recursive calls

0 db block gets

1532 consistent gets

1527 physical reads

0 redo size

900 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

5 rows processed

dao@>

--选择对象最大的第六到第十行

dao@>select *

2 from

3 (select rownumrn,object_id,object_name

4 from (

5 selectobject_id,object_name

6 from dao_objects d

7 order by object_iddesc )

8 )

9 wherern between 6 and 10 ;

RN OBJECT_ID OBJECT_NAME

---------- ---------- --------------------------------------------------------------------------------------------------------------------------------

6 92000 WRI$_OPTSTAT_HISTHEAD_HISTORY

7 91999 WRI$_ADV_SEGADV_SEGROW

8 91998 WRI$_ADV_OBJSPACE_CHROW_DATA

9 91997 WRI$_ADV_OBJSPACE_TREND_DATA

10 91991 WRP$_REPORTS_TIME_BANDS

Execution Plan

----------------------------------------------------------