存储过程 分页

[code]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;[/code]

[code]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;[/code]


[code]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); //结果集
[/code][url]