存储过程 分页

CREATE OR REPLACE PACKAGE CURSPKG AS
 TYPE refCursorType IS REF CURSOR;
  procedure sp_Page(p_PageSize       int, --每页记录数
                    p_PageNo         int, --当前页码,从 1 开始
                    p_SqlSelect      varchar2, --查询语句,含排序部分
                    p_SqlCount       varchar2, --获取记录总数的查询语句
                    p_OutRecordCount out int, --返回总记录数
                    p_OutCursor      out refCursorType);
END;

create or replace package body CURSPKG is

  procedure sp_Page(p_PageSize       int, --每页记录数
                    p_PageNo         int, --当前页码,从 1 开始
                    p_SqlSelect      varchar2, --查询语句,含排序部分
                    p_SqlCount       varchar2, --获取记录总数的查询语句
                    p_OutRecordCount out int, --返回总记录数
                    p_OutCursor      out refCursorType) is
    v_sql       varchar2(3000);
    v_count     int;
    v_heiRownum int;
    v_lowRownum int;
  begin
  
    ----取记录总数
    execute immediate  p_SqlCount
      into v_count;
    p_OutRecordCount := v_count;
    ----执行分页查询
    v_heiRownum := p_PageNo * p_PageSize;
    v_lowRownum := v_heiRownum - p_PageSize + 1;
  
    v_sql := 'SELECT * FROM (SELECT A.*, rownum rn 
    FROM (' ||p_SqlSelect || ') A
    WHERE rownum <= ' || to_char(v_heiRownum) || ') B
    WHERE rn >= ' || to_char(v_lowRownum);
    --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
  
    OPEN p_OutCursor FOR v_sql; 
  end sp_Page;

end;

sqlstr = "select ID,From_UserID,To_UserID,to_char(AddDate,'YYYY-MM-DD HH24:MM:SS') as AddDate,Content,IsRead,State,From_UserName,To_UserName,Title from VW_tData_Inquire";
sqlstrcount = "select count(*) from VW_tData_Inquire";
openDB();
			String sql = "{ call CURSPKG.sp_Page(?,?,?,?,?,?)}";
	        CallableStatement proc = conn.prepareCall(sql);
	        proc.setInt(1, p_PageSize);        		//每页数量
	        proc.setInt(2, p_PageNo);          		//页码
	        proc.setString(3, sqlstr);      		//取数据的sql
	        proc.setString(4, sqlstrcount);        	//取数据个数的sql
	        proc.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);      //输出数据行数
	        proc.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);      //输出游标记录集
	        proc.execute();
	        RowsCount = ( (OracleCallableStatement) proc).getInt(5);	//总行数
	        rs = (ResultSet)proc.getObject(6);	//结果集

[url]