程序
package com.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import com.jdbc.BaseJdbcDAO;import oracle.jdbc.OracleTypes;public class Test {/*** @param args*/public static void main(String[] args) {Connection conn = BaseJdbcDAO.getConnection2("com");String sqlSel = "SELECT * FROM POSITION";String sqlCount = "SELECT COUNT(*) FROM POSITION";String sql = "{ call SP_PAGE(?,?,?,?,?,?,?) }";ResultSet rs = null;try {CallableStatement call = conn.prepareCall(sql);call.setInt(1, 20);call.setInt(2, 100);call.setString(3, sqlSel);call.setString(4, sqlCount);call.registerOutParameter(5, Types.INTEGER);call.registerOutParameter(6, Types.INTEGER);call.registerOutParameter(7, OracleTypes.CURSOR);call.execute();// 取出结果集int pageCount = call.getInt(5);//总页数int total = call.getInt(6);//总记录数rs = (ResultSet) call.getObject(7);//信息列表集System.out.println("页总数：" + pageCount);System.out.println("记录总数：" + total);int i = 1;while (rs.next()) {System.out.println(">>" + i + "号码：" + rs.getString(2) + "名称："+ rs.getString(3) + "序号:" + rs.getInt(1));i++;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/* 何问起 hovertree.com */
--创建游标包
create or replace PACKAGE "PKG_QUERY" AStype refCursorType is REF CURSOR; --游标类型定义，用于返回数据集END;/* 何问起 hovertree.com */
--存储过程
create or replace procedure sp_Page(p_PageSize int, --每页记录数p_PageNo int, --当前页码,从 1 开始p_SqlSelect varchar2, --查询语句,含排序部分p_SqlCount varchar2, --获取记录总数的查询语句p_pageCount out int,--总共多少页p_OutRecordCount out int,--返回总记录数p_OutCursor out PKG_QUERY.refCursorType)asv_sql varchar2(3000);v_count int;v_heiRownum int;v_lowRownum int; begin ----取记录总数 execute immediate p_SqlCount into v_count; p_OutRecordCount := v_count; --计算mypageCount-- if mod(v_count,p_PageSize)=0 then p_pageCount:=v_count/p_PageSize; else p_pageCount:=v_count/p_PageSize+1; end if; ----执行分页查询 v_heiRownum := p_PageNo * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; v_sql := 'SELECT B.* FROM (SELECT A.*, rownum rn FROM ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) || ' ) B WHERE rn >= ' || to_char(v_lowRownum) ; --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rnOPEN p_OutCursor FOR v_sql;end sp_Page;/* 何问起 hovertree.com */ 推荐: