概述

oracle的分頁一共有三種方式，但在Oracle中實現分頁的方法主要是用ROWNUM關鍵字和用ROWID關鍵字兩種，下面介紹下:

“網上流傳的名人名言80%以上都是假的”——亞拉伯罕·林肯

方法一根據rowid來分

SELECT *
 FROM EMP
 WHERE ROWID IN
 (SELECT RID
 FROM (SELECT ROWNUM RN, RID
 FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
 WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每頁顯示幾條
 WHERE RN > ((currentPage-1) * pageSize) ) --當前頁數
 ORDER BY EMPNO DESC;
 
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每頁顯示幾條
-- 0 = (currentPage-1) * pageSize 當前頁數
SELECT *
 FROM EMP
 WHERE ROWID IN
 (SELECT RID
 FROM (SELECT ROWNUM RN, RID
 FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
 WHERE ROWNUM <= ( (1-1) * 5 + 5 )) --每頁顯示幾條
 WHERE RN > ((1-1) * 5) ) --當前頁數
 ORDER BY EMPNO DESC;

方法二按分析函數來分

SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= ( (currentPage-1) * pageSize + pageSize ) --每頁顯示幾條
AND RK > ( (currentPage-1) * pageSize ); --當前頁數
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每頁顯示幾條
-- 0 = (currentPage-1) * pageSize 當前頁數
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= 5
AND RK > 0;

方法三按rownum 來分

SELECT *
 FROM (SELECT T.*, ROWNUM RN
 FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
 WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每頁顯示幾條
 WHERE RN > ( (currentPage-1) * pageSize ); --當前頁數
 
eg、
-- 5 = (currentPage-1) * pageSize + pageSize 每頁顯示幾條
-- 0 = (currentPage-1) * pageSize 當前頁數
SELECT *
 FROM (SELECT T.*, ROWNUM RN
 FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
 WHERE ROWNUM <= 5)
 WHERE RN > 0;