Oracle分页整理大全

注：根据视频及资料转载整理而来

分页查询

分页查询格式：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

Oracle数据库使用存储过程实现分页

1、创建包同时创建游标

create or replace package pagingPackage is  type paging_cursor is ref cursor;end;/

 

2、创建分页存储过程

create or replace procedure paging_cursor(    v_in_table in varchar2,    v_in_pagesize in number,    v_in_pagenow in number,    v_out_result out pagingPackage.paging_cursor,    v_out_rows out number,    v_out_pagecount out number) is    --定义需要的变量    v_sql varchar2(4000);    v_sql_select varchar2(4000);    v_start number;    v_end number;begin    --计算v_start和v_end是多少    v_start:=v_in_pagesize*(v_in_pagenow-1)+1;    v_end:=v_in_pagesize*v_in_pagenow;    v_sql:='select t2.* from (select t1.*,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;        --打开游标，让游标指向结果集    open v_out_result for v_sql;        --查询共有多少条记录    v_sql_select:='select count(*) from '||v_in_table;    execute immediate v_sql_select into v_out_rows;       --统计多少页记录    if mod(v_out_rows,v_in_pagesize)=0 then        v_out_pagecount:=v_out_rows/v_in_pagesize;    else        v_out_pagecount:=v_out_rows/v_in_pagesize+1;    end if;        --关闭游标    close v_out_result;end;/

 

3、java调用分页存储过程(完整)源代码

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;public class TestProcedurePaging {    // 调用oracle分页存储过程并获得存储过程的返回结果集。    public static void main(String[] args) {        // 定义需要的变量        Connection ct = null;        CallableStatement cs = null;        ResultSet rs = null;        try {            // 加载驱动            Class.forName("oracle.jdbc.driver.OracleDriver");            // 得到连接            ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");            // 创建CallableStatement接口            cs = ct.prepareCall("{call paging_cursor(?,?,?,?,?,?)}");            // 给in?赋值            cs.setString(1, "emp");// 传表名            cs.setInt(2, 6);// 传入pagesize，每页显示多少条记录            cs.setInt(3, 1);// 传入pagenow，显示第几页。            // 给out?注册            cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);            cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);            cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);            // 执行            cs.execute();            // 这里是关键所在，java没有接收结果集的get方法，所以只能用getObject来接收结果集，接收到后需要使用ResultSet强转才可以            rs = (ResultSet) cs.getObject(4);            // 循环取出            while (rs.next()) {                System.out.println(rs.getString("ename") + " " + rs.getString("sal"));            }            // 取出总记录数            int rowCount = cs.getInt(5);            // 取出总页数            int pageCount = cs.getInt(6);            System.out.println("共有记录：" + rowCount + "条!   " + "共有记录：" + pageCount + "页!");        } catch (Exception e) {            e.printStackTrace();        } finally {            // 关闭资源            try {                if (cs != null) {                    cs.close();                }                if (rs != null) {                    rs.close();                }                if (ct != null) {                    ct.close();                }            } catch (Exception e) {                e.printStackTrace();            }            cs = null;            rs = null;            ct = null;        }    }}