在Oracle数据库中实现分页通常有以下几种方法，每种方法都有其适用场景和优缺点。
1. 使用ROWNUM
ROWNUM是Oracle为结果集的每一行分配的一个唯一的数字，这个数字表示行被检索出来的顺序。但是，需要注意的是，ROWNUM是在结果集产生之后才被赋予的，并且只能在一个查询中直接引用一次。因此，使用ROWNUM进行分页时，常常需要嵌套查询。
示例：
假设我们有一个名为employees的表，我们想获取第n页的数据，每页显示m条记录。
SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT * FROM employees ORDER BY employee_id
) a
WHERE ROWNUM <= (n-1)*m + m
)
WHERE rnum > (n-1)*m;
这里，(n-1)*m + m表示从第一行到第n页最后一行的ROWNUM范围，而外部查询则用于去除掉不需要的前(n-1)*m行记录。
2. 使用FETCH FIRST ... ROWS ONLY 和 OFFSET ... ROWS（Oracle 12c及以后版本）
从Oracle 12c开始，Oracle引入了FETCH FIRST ... ROWS ONLY和OFFSET ... ROWS子句来简化分页查询。这种方法比使用ROWNUM更加直观和易于理解。
示例：
SELECT * FROM employees
ORDER BY employee_id
OFFSET (n-1)*m ROWS FETCH NEXT m ROWS ONLY;
这里，OFFSET (n-1)*m ROWS表示跳过前(n-1)*m行记录，FETCH NEXT m ROWS ONLY表示接着取m行记录。
3. 使用第三方工具或框架
在实际开发中，我们往往不会直接写SQL进行分页，而是使用ORM框架（如Hibernate、MyBatis等）或数据库访问工具（如Spring Data JPA）等提供的分页功能。这些工具或框架通常会封装好分页的逻辑，让我们只需要提供分页的参数（如页码和每页显示的记录数）即可。
4. 使用存储过程的分页
4.1. 首先创建一个包package_cursor 并定义一个类型 p_cursor 是一个游标
create or replace package package_hr_emp
as
type type_cursor is ref cursor;
type type_record is record
( empno EMPLOYEES.EMPLOYEE_ID%TYPE,
empname EMPLOYEES.FIRST_NAME%TYPE,
lastname EMPLOYEES.LAST_NAME%TYPE,
email EMPLOYEES.EMAIL%TYPE,
phone_number EMPLOYEES.PHONE_NUMBER%TYPE,
hire_date EMPLOYEES.HIRE_DATE%TYPE,
job_id EMPLOYEES.JOB_ID%TYPE,
salary EMPLOYEES.SALARY%TYPE,
comm_pct EMPLOYEES.COMMISSION_PCT%TYPE,
manager_id EMPLOYEES.MANAGER_ID%TYPE,
dept_id EMPLOYEES.DEPARTMENT_ID%TYPE,
Rn number
);
end;
/
4.2. 创建分页存储过程
CREATE OR REPLACE PROCEDURE sp_GetPageSizeList_Plsql
(p_tablename IN VARCHAR2,--分页的表
p_Rows IN NUMBER,--每页显示的条数
p_Current IN NUMBER,--当前是第几页
p_RowCount OUT NUMBER,--总结果行数
p_PageSum OUT NUMBER,--总页数
p_CursorList OUT package_hr_emp.type_cursor -- 输出结果集
)
IS
v_sqlstr VARCHAR2(2000) ;
v_begin NUMBER:=(p_Current-1)*p_Rows+1;--每页开始的行序号
v_end NUMBER:=p_Current*p_Rows;
BEGIN
v_sqlstr:='SELECT * FROM (SELECT T.*,rownum rn FROM (SELECT * FROM '|| p_tablename ||') T WHERE rownum<='||v_end ||') WHERE rn >='||v_begin;
-- 把游标和sql 关联起来
OPEN p_CursorList FOR v_sqlstr;
-- 计算总结果记录行数
v_sqlstr:='SELECT COUNT(*) FROM '|| p_tablename ;
EXECUTE IMMEDIATE v_sqlstr INTO p_RowCount ;-- 执行SQL 并把结果赋值给 p_RowCount
IF MOD(p_RowCount,p_Rows)=0 THEN
p_PageSum:=p_RowCount/p_Rows;
ELSE
p_PageSum:=CEIL(p_RowCount/p_Rows);
END IF;
--关闭游标
--CLOSE p_CursorList; 如果打开java程序则ResultSet为NULL
END;
/
4.3. sqlplus 调用方法
set serverout on
-- 每页显示10行，显示第一页的数据
HR@orcl> declare
2 cur_out_arg package_hr_emp.type_cursor;
3 rec_arg package_hr_emp.type_record;
4 v_RowCount NUMBER;
5 v_PageSum NUMBER;
6 begin
8 dbms_output.put_line('------------------------');
10 sp_GetPageSizeList_Plsql('employees',10,1,v_RowCount,v_PageSum,cur_out_arg);
12 dbms_output.put_line('总结果集行数为：'||v_RowCount||',总页数为：'||v_PageSum);
14 loop
15 fetch cur_out_arg into rec_arg;
16 exit when cur_out_arg%notfound;
17 dbms_output.put_line(rec_arg.empno || ' ' || rec_arg.empname || ' ' ||rec_arg.lastname || ' ' || rec_arg.email || ' ' ||rec_arg.phone_number);
18 end loop;
19 end;
20 /
------------------------
总结果集行数为：107,总页数为：11
100 Steven King SKING 515.123.4567
101 Neena Kochhar NKOCHHAR 515.123.4568
102 Lex De Haan LDEHAAN 515.123.4569
103 Alexander Hunold AHUNOLD 590.423.4567
104 Bruce Ernst BERNST 590.423.4568
105 David Austin DAUSTIN 590.423.4569
106 Valli Pataballa VPATABAL 590.423.4560
107 Diana Lorentz DLORENTZ 590.423.5567
108 Nancy Greenberg NGREENBE 515.124.4569
109 Daniel Faviet DFAVIET 515.124.4169
PL/SQL procedure successfully completed.
-- 每页显示5行，显示第二页的数据
HR@orcl> declare
2 cur_out_arg package_hr_emp.type_cursor;
3 rec_arg package_hr_emp.type_record;
4 v_RowCount NUMBER;
5 v_PageSum NUMBER;
6 begin
8 dbms_output.put_line('------------------------');
10 sp_GetPageSizeList_Plsql('employees',5,2,v_RowCount,v_PageSum,cur_out_arg);
12 dbms_output.put_line('总结果集行数为：'||v_RowCount||',总页数为：'||v_PageSum);
14 loop
15 fetch cur_out_arg into rec_arg;
16 exit when cur_out_arg%notfound;
17 dbms_output.put_line(rec_arg.empno || ' ' || rec_arg.empname || ' ' ||rec_arg.lastname || ' ' || rec_arg.email || ' ' ||rec_arg.phone_number);
18 end loop;
19 end;
20 /
------------------------
总结果集行数为：107,总页数为：22
105 David Austin DAUSTIN 590.423.4569
106 Valli Pataballa VPATABAL 590.423.4560
107 Diana Lorentz DLORENTZ 590.423.5567
108 Nancy Greenberg NGREENBE 515.124.4569
109 Daniel Faviet DFAVIET 515.124.4169
PL/SQL procedure successfully completed.
4.4. Java调用分页存储过程
package com.mao;
import java.sql.*;
public class procedurepagesize {
public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";
public static final String DBURL = "jdbc:oracle:thin:@192.168.0.207:1521/FREEPDB1";
public static final String USER = "hr";
public static final String PASSWORD = "hr";
public static void main(String[] args) {
try {
Class.forName(DBDRIVER);
Connection conn = DriverManager.getConnection(DBURL, USER, PASSWORD);
Statement stmt = conn.createStatement(); //Statement
// 调用分页存储过程 sp_GetPageSizeList
System.out.println("********调用分页存储过程 ********");
String mSQL2 = " {CALL sp_GetPageSizeList(?,?,?,?,?,?) }";
CallableStatement callableStatement_pagesize = conn.prepareCall(mSQL2);
callableStatement_pagesize.setString(1, "EMPLOYEES");
callableStatement_pagesize.setInt(2, 5);//每页显示的条数
callableStatement_pagesize.setInt(3, 2);//当前是第几页
// 注册总行数
callableStatement_pagesize.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
// 注册总页数
callableStatement_pagesize.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
callableStatement_pagesize.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
callableStatement_pagesize.execute();//执行
int rowCount = callableStatement_pagesize.getInt(4);
int pageSum = callableStatement_pagesize.getInt(5);
// 结果集
ResultSet resultSet_pagesize = (ResultSet) callableStatement_pagesize.getObject(6);
System.out.println("总行数:" + rowCount);
System.out.println("总页数:" + pageSum);
while(resultSet_pagesize.next()) { //移动指针的同时判读是否还有数据行
int empno=resultSet_pagesize.getInt(1);
String ename=resultSet_pagesize.getString(2);
System.out.println("PeaparedStatement class method id ="+empno+", xingm_nameinfo ="+ename);
}
conn.close();
stmt.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
com.mao.procedurepagesize
********调用分页存储过程 ********
总行数:107
总页数:22
PeaparedStatement class method id =105, xingm_nameinfo =David
PeaparedStatement class method id =106, xingm_nameinfo =Valli
PeaparedStatement class method id =107, xingm_nameinfo =Diana
PeaparedStatement class method id =108, xingm_nameinfo =Nancy
PeaparedStatement class method id =109, xingm_nameinfo =Daniel
Process finished with exit code 0
5. 注意事项
- 在使用分页查询时，一定要指定
ORDER BY子句，以确保结果的一致性。 - 对于大量数据的分页查询，性能可能会成为问题。考虑使用索引优化查询，或者根据业务需求调整分页逻辑（如懒加载、搜索条件过滤等）。
- 不同的Oracle版本可能在分页查询的支持上有所不同，务必参考对应版本的官方文档。
扫一扫
3326
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
