Oracle分页存储过程

 话不多说，分页存储过程如下：

CREATE OR REPLACE PROCEDURE "GETRECORDFROMPAGE" 
(
        p_colunmName        in varchar2,   --列名
        p_tableName        in varchar2,   --表名
        p_strWhere         in varchar2,   --查询条件
        p_orderColumn      in varchar2,   --排序的列
        p_orderStyle       in varchar2,   --排序方式
        p_curPage          in Number, --当前页
        p_pageSize         in Number, --每页显示记录条数
        p_totalRecords     out Number,     --总记录数
        p_totalPages       out Number,    --总页数
        v_cur              out Sys_Refcursor --返回的结果集
)
is
   v_sql VARCHAR2(2000) := '';      --sql语句
    v_startRecord Number(8);          --开始显示的记录条数
    v_endRecord Number(8);           --结束显示的记录条数
begin
      --记录中总记录条数
v_sql:='select to_number(count(*)) from ' || p_tableName||' ' || 'where 1=1 ';
      if p_strWhere is not null or p_strWhere <> '' then
         v_sql:=v_sql || ' and ' ||p_strWhere;
      end if;
    Dbms_Output.put_line(v_sql);
    execute immediate v_sql into p_totalRecords;

    --验证页面记录大小
     if p_pageSize<0 then
        p_pageSize:=0;
     end if;

     --根据页大小计算总页数
if mod(p_totalRecords,p_pageSize) =0 then
       p_totalPages := p_totalRecords /p_pageSize;
     else
       p_totalPages := p_totalRecords / p_pageSize + 1;
     end if;

    --验证页号
     if p_curPage<1 then
        p_curPage:=1;
     end if;

     if p_curPage > p_totalPages then
        p_curPage:=p_totalPages;
     end if;

    --实现分页查询
     if p_totalRecords = 0 then --如果总记录数为0
       v_startRecord :=0;
       v_endRecord :=0;
     else
       v_startRecord:=(p_curPage-1)* p_pageSize+1;
       v_endRecord :=p_curPage* p_pageSize;
     end if;

     v_sql := 'select * from (select rownum tt,'|| p_colunmName ||' from ' || p_tableName;

     if p_strWhere is not null or p_strWhere <> '' then
       v_sql := v_sql || ' where 1=1 and ' || p_strWhere;
     end if;

     if p_orderColumn IS NOT NULL or p_orderColumn <> '' then
       v_sql := v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle;
     end if;

       v_sql := v_sql || ') t where  t.tt >= ' || v_startRecord;
       v_sql := v_sql || ' and   t.tt < ' || v_endRecord;
     DBMS_OUTPUT.put_line(v_sql);

     open v_cur for v_sql;
end GetRecordFromPage; 

Java中调用存储过程如下：

	public static Page getPro(String column,String tableName,String where,String order,
			String orderStyle,Class clazz) throws Exception
	{
		ArrayList list = new ArrayList();
		Connection conn = null;
		CallableStatement cst = null;
		ResultSet rs = null;
		conn = DBHelper.getConn();
		Page page = new Page();
		try {
			cst = conn.prepareCall("{call GetRecordFromPage(?,?,?,?,?,?,?,?,?)}");
			cst.setString(1,column);
			cst.setString(2, tableName);
			cst.setString(3, where);
			cst.setString(4, order);
			cst.setString(5, orderStyle);
			cst.setInt(6, page.getPageIndex());
			cst.setInt(7, page.getPageSize());
			cst.registerOutParameter(8, java.sql.Types.INTEGER);
			cst.registerOutParameter(9, java.sql.Types.INTEGER);
                           cst.registerOutParameter(10, OracleTypes.CURSOR);
			cst.execute();
			 rs = (ResultSet) cst.getObject(10);
			ResultSetMetaData rsmd = rs.getMetaData();
			while(rs.next())
			{									for (int i = 1; i <= rmd.getColumnCount(); i++) {
      fieldName = rmd.getColumnName(i);
      Field field = clazz.getDeclaredField(fieldName);
      String stringLetter = fieldName.substring(0, 1)
        .toUpperCase();
      // 获得相应属性的setXXX方法名称
      String setName = "set" + stringLetter
        + fieldName.substring(1);
      Method setMethod = clazz.getMethod(setName,
        new Class[] { field.getType() });
      // 获得参数值
      setMethod.invoke(bean, new Object[] { getObjectFromRs(
        rs, fieldName, field.getType()) });
     }
     list.add(bean);}}catch (Exception e) {
   // TODO: handle exception
   e.printStackTrace();
  }
  finally{
   rs.close();
   cst.close();
   conn.close();
  }
page.setData(list);
return page;
}