Oracle分页存储过程

1.首先创建存储过程的包头，代码如下：

CREATE OR REPLACE PACKAGE Common_Package   as
procedure p_pagintion 
(    
       selectsql in varchar2,       --条件SQL语句    
       curpagenum in number,       --第几页    
       pagesize in number,        --一页显示多少条    
       pagecount out number,      --共几页    
       notecount out number,  --总记录    
       currentPage out SYS_REFCURSOR      --显示页的记录    
); 
end;

2.然后再创建存储过程的包体，代码如下：

CREATE OR REPLACE PACKAGE body Common_Package   as
procedure p_pagintion 
(    
       selectsql in varchar2,       --条件SQL语句    
       curpagenum in number,       --第几页    
       pagesize in number,        --一页显示多少条    
       pagecount out number,      --共几页    
       notecount out number,  --总记录    
       currentPage out SYS_REFCURSOR      --显示页的记录    
) is 
v_pfrist number;    
v_sql varchar2(100);    
v_sql1 varchar2(100);    
v_sql2 varchar2(100);    
v_sql3 varchar2(1000);    
v_notecount number;    
v_min number;    
v_max number; 
begin 
            v_sql:='select count(*) from (' || selectsql || ')';--查询总记录的SQL    
            execute immediate v_sql into v_notecount;--执行SQL把总记录结果得到    
            notecount :=v_notecount;    
            pagecount :=ceil(notecount/pagesize);--求出总页数    
            v_pfrist:=curpagenum;    
            IF(v_pfrist > pagecount)THEN    
            v_pfrist:=pagecount; -- 如果传进来的页数大于最后一页，默认返回最后一页数据    
            end IF;    
            v_max:=v_pfrist*pagesize;--显示页的最大记录排行值    
            v_min:=v_max-pagesize+1;--显示页的最小记录排行值    
            v_sql1:='select * from (select rownum rn,t.* from ';    
            v_sql2:=' t ) where rn between '||v_min||' and '||v_max;    
            v_sql3:=v_sql1||' ( '|| selectsql ||' ) '||v_sql2;--分页SQL    
            open currentPage for v_sql3;    
END p_pagintion; 
end;

3.PL/SQL下的测试代码，不过没有打印出当前页的内容

DECLARE 
  pageCnt int;
  noteCnt int;
  curPage sys_refcursor;
BEGIN
        Common_Package.p_pagintion('select * from Student',1,10,pageCnt,noteCnt,curPage);
        dbms_output.put_line(pageCnt);
        dbms_output.put_line(noteCnt);
END;

4.Java调用测试代码

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

import oracle.jdbc.OracleTypes;

public class TestOracleProcedure {
	public static void main(String[] args) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		CallableStatement cstmt = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:orcl", "Ajita", "123456");
			cstmt = conn.prepareCall("{call Common_Package.p_pagintion(?,?,?,?,?,?)}");
			cstmt.setString(1, "select t.* from BizBusinessBaseInfo t");
			cstmt.setInt(2, 3);
			cstmt.setInt(3, 5);
			cstmt.registerOutParameter(4, OracleTypes.NUMBER);
			cstmt.registerOutParameter(5, OracleTypes.NUMBER);
			cstmt.registerOutParameter(6, OracleTypes.CURSOR);
			cstmt.executeQuery();
			System.out.println("共" + cstmt.getObject(4) + "页");
			System.out.println("共" + cstmt.getObject(5) + "条");
			rs = (ResultSet) cstmt.getObject(6);
			while (rs.next()) {
				System.out.println(rs.getString("BizName") + " -- "
						+ rs.getString("BossProdName") + " -- " + rs.getString("BizStatus"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs.close();
			cstmt.close();
			conn.close();
		}
	}
}