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

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

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

--创建包-- Author : Sugar-- Created : 2014-07-04-- Purpose : 分页过程create or replace package p_page isTYPE 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 isPROCEDURE Pagination(Pindex in number,Psql in varchar2,Psize in number,Pcount out number,v_cur out type_cur)ASv_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)asv_sql varchar2(1000);v_prcount number;beginv_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; //创建statementResultSet rs = null;  //创建结果集Connection conn = null; //构造数据库 会话CallableStatement proc = null; // 创建 CallableStatement 对象 用于调用存储过程try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, "scott", "tiger");//调用存储过程 PAGINATIONproc = 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>");}//调用存储过程 PAGERECORDSCOUNTproc = 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  初学者理解存储过程 ，非常具有实用效果  ，若有疑问，欢迎拍砖~~