Oracle数据库中实现分页

大家好，欢迎来到IT知识分享网。

在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版本可能在分页查询的支持上有所不同，务必参考对应版本的官方文档。