oracle存储过程实现分页

首先我们来谈谈PL/SQL的好处
1.块结构： PL/SQL代码是由彼此之间可以互相嵌套的快组成的。每个块形成一个任务或逻辑模块单元。PL/SQL的块可以存储在数据库中，并且可以被重用。

2.过程语言能力：PL/SQL由过程语言的结构组成，像条件语句（if else语句）和循环语句（for loops）。

3.更好的性能：PL/SQL引擎可以同时处理多条SQL语句像一个单独的块，因此节约了网络传输。

4.错误处理：在PL/SQL程序执行期间，PL/SQL可以有效的处理异常或者错误。一旦异常被捕获，相应的动作可以被执行，依赖于异常的类型，或者显示个客户一个错误的消息。

下面是要实现分页用到的包和存储过程

--定义一个包，并在包中定义一个取得分页结果集的游标
create or replace package fypackage as
type fenye_cursor is ref cursor;
end fypackage;

--定义一个存储过程用来表示分页
create or replace procedure fenye(
tableName in varchar2,--查询的表名
myPageSize in number,--每页显示记录条数
pageNum in number,--第几页
allRows out number,--所有记录条数
allPages out number,--所有页数
result_cursor out fypackage.fenye_cursor--返回结果集游标
) is
v_sql varchar2(1000);
v_start number:=(pageNum-1)*myPageSize+1;
v_end number:=pageNum*myPageSize;
begin
v_sql:='select * from (select A.*,rownum rn from (select * from '||tableName||') A) where rn>='||v_start||' and rn<='||v_end;
open result_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into allRows;
if mod(allRows,myPageSize)=0 then
allPages:=allRows/myPageSize;
else
allPages:=allRows/myPageSize+1;
end if;
end;

在客户端调用存储过程

package com.lamp.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestProcedureFenye {

	public static void main(String[] args) {
		Connection conn = null;
		CallableStatement cs = null;
		String user = "SCOTT";
		String password = "tiger";
		String url = "jdbc:oracle:thin:@localhost:1521:数据库名";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, user, password);
			cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
			cs.setString(1, "emp");
			cs.setInt(2, 5);
			cs.setInt(3, 2);
			// 设置返回值类型
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
			cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
			cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			cs.execute();
			int allRows = cs.getInt(4);
			int allPages = cs.getInt(5);
			ResultSet rs = (ResultSet) cs.getObject(6);
			System.out.println(allRows + " " + allPages);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (cs != null) {
				try {
					cs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}