Oracle 高效分页存储过程实例 含javademo

  关于分页，想必是每一程序猿都会遇到的问题，解决办法有很多，  接下来就让我们尝试下 oracle 存储过程实现，直接在数据库层面实现，重点在于高效 

1首先创建 存储过程用的包，以及声明存储过程

--创建包
-- Author : Sugar
-- Created : 2014-07-04
-- Purpose : 分页过程
create or replace package p_page is

TYPE type_cur IS REF CURSOR; -- 定义游标变量用于返回记录集
PROCEDURE Pagination(
Pindex in number, -- 分页索引
Psql in varchar2, -- 产生 dataset 的 sql 语句
Psize in number, -- 页面大小
Pcount out number, -- 返回分页总数
v_cur out type_cur -- 返回当前页数据记录
);
procedure PageRecordsCount(
Psqlcount in varchar2, -- 产生 dataset 的 sql 语句
Prcount out number -- 返回记录总数
);
end p_page;

2接下来创建包体，和存储过程的实现

--创建包体
create or replace package body p_page is

PROCEDURE Pagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
AS
v_sql VARCHAR2(1000);
v_count number;
v_Plow number;
v_Phei number;
Begin
------------------------------------------------------------ 取分页总数
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------ 显示任意页内容
v_Phei := Pindex * Psize + Psize;--得到记录上限
v_Plow := v_Phei - Psize + 1; --得到记录下限
--例如：  Psql := 'select rownum rn,t.* from emp t' ; -- 要求必须包含 rownum 字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
open v_cur for v_sql;
End Pagination;

procedure PageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; -- 返回记录总数
end PageRecordsCount;
end p_page;

3现在我们的 sql脚本已经 完成，接下来可以现在 pl/sql中测试一遍我们写的 存储过程是否正确，pl/sql 集成了测试工具，我们直接使用即可 

我们可是用系统自带的 emp表去测试 ，我的测试结果如下：

4 测试成功后 ，我们接下来 将存储过程应用到 java工程当中去  

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestProcedureDemo3 {
	public static void main(String[] args) {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String strUrl = "jdbc:oracle:thin:@192.168.1.100:1521:orcl"; //本机IP不能用localhost取代
		Statement stmt = null; //创建statement
		ResultSet rs = null;  //创建结果集
		Connection conn = null; //构造数据库 会话
		CallableStatement proc = null; // 创建 CallableStatement 对象 用于调用存储过程
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(strUrl, "scott", "tiger");
			
			
			//调用存储过程 PAGINATION
			proc = conn.prepareCall("{ call  P_PAGE.PAGINATION(?,?,?,?,?) }");
			//设置输入参数
			proc.setInt(1, 0);
			proc.setString(2, "select rownum rn,t.* from emp t");
			proc.setInt(3, 3);
			//绑定输出参数类型
			proc.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);
			proc.registerOutParameter(5,oracle.jdbc.OracleTypes.CURSOR);
			proc.execute();
			int number = proc.getInt(4);
			System.out.println("当前分页总数"+ number);
			rs = (ResultSet) proc.getObject(5);
			while (rs.next()) {
				System.out.println("<tr><td>" + rs.getString(1) +
						"</td><td>"
						+ rs.getString(2) 
						+ "</td></tr>"+ rs.getString(3) +
						"</td></tr>"+ rs.getString(4) + "</td></tr>");
			}
			//调用存储过程 PAGERECORDSCOUNT
			proc = conn.prepareCall("{ call  P_PAGE.PAGERECORDSCOUNT(?,?) }");
			proc.setString(1, " emp ");
			proc.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBER);
			proc.execute();
			int recordCount = proc.getInt(2);
			System.out.println("当前记录总数"+ recordCount);
			
		} catch (SQLException ex2) {
			ex2.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					if (stmt != null) {
						stmt.close();
					}
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException ex1) {
			}
		}
	}
}

5  备注，java工程中需要导入 oracle 驱动包（odbc14.jar） 运行即可出现如下效果 ，应用成功

  以上即是本人的学习笔记，虽然不多，但是非常适合oracle  初学者理解存储过程 ，非常具有实用效果  ，若有疑问，欢迎拍砖~~