java Oracle存储过程分页

创建一个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();

--分页中我们常用的SQL
--查询第1条到第10条之间的数据
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY HATSU_CD) CT,
ALL_COLUMN.*
FROM (SELECT * FROM BID_HISTORY T) ALL_COLUMN
ORDER BY HATSU_CD)
WHERE CT >=1 AND CT <= 10
--查询第1条到第15条之间的数据，这个办法应该说相对会低一些。
SELECT A.*, A.A
FROM (SELECT T.*, ROWNUM A FROM BID_HISTORY T WHERE ROWNUM < 15) A
WHERE A.A > 10

--有时候我们需要根据不同的条件俩查询数据
--多于两个条件的情况
SELECT T.HATSU_CD, URL, LOCALE, KUBUN_CD
FROM BID_URLDEF T
WHERE (T.HATSU_CD = 'BUYER01' AND T.KUBUN_CD = 'MailUrl' AND
LOCALE = 'js_JP') OR
((SELECT COUNT(*)
FROM BID_URLDEF T
WHERE (T.HATSU_CD = 'BUYER01' AND T.KUBUN_CD = 'MailUrl' AND
LOCALE = 'js_JP')) = 0 AND T.HATSU_CD = 'BUYER01' AND
T.KUBUN_CD = 'MailUrl' AND LOCALE = 'en_US')
--有且仅有两种条件的情况，这个时候下面的SQL比上面的要高
SELECT T.*
FROM BID_URLDEF T
WHERE T.LOCALE = (CASE WHEN EXISTS
(SELECT 'X' FROM BID_URLDEF TT WHERE TT.LOCALE = 'ja_JP') THEN
'ja_JP' ELSE 'en_US' END) AND T.HATSU_CD = 'BUYER01' AND
T.KUBUN_CD = 'MailUrl'