创建一个package:
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;
Package Body:
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;
Java方法里调用：
.....................
Class.forName(db_driver);
conn = DriverManager.getConnection(db_url,db_username,db_password);
String sql = "{ call CURSPKG.SP_PAGE(?,?,?,?,?,?)}";
proc = conn.prepareCall(sql);
proc.setInt(1, 10); //每页数量
proc.setInt(2, 2); //页码
proc.setString(3, "select * from test_table"); //取数据的sql
proc.setString(4, "select count(*) from test_table"); //取数据个数的sql
proc.registerOutParameter(5, OracleTypes.INTEGER); //输出数据行数
proc.registerOutParameter(6, OracleTypes.CURSOR); //输出游标记录集
proc.execute();
int r = ( (OracleCallableStatement) proc).getInt(5);
rs = ( (OracleCallableStatement) proc).getCursor(6);
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
proc.close();
conn.close();
