create or replace package paging as
type page_cursor is ref cursor;
end paging;
SQL> create or replace package paging as
2 type page_cursor is ref cursor;
3 end paging;
4 /
Package created
create or replace procedure pageing
(
tableName in varchar2,
mpageSize in number,
nowPage in number,
countRecord out number,
countPages out number,
record_cursor out paging.page_cursor
) is
--定义sql语句
v_sql varchar(1000);
v_beginRecord number:=(nowPage-1)*mpageSize+1;
v_endRecord number:=nowPage*mpageSize;
begin
v_sql:='select * from (select '|| tableName ||'.*,rownum rn from '|| tableName ||')
where rn>='|| v_beginRecord ||' and rn<='|| v_endRecord ||' order by sal';
--把游标和sql语句关联，将查询给游标
open record_cursor for v_sql;
--计算countRecord和conutPages
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into countRecord;
--计算countPages
if mod(countRecord,mpageSize)=0 then
countPages:=countRecord/mpageSize;
else
countPages:=countRecord/mpageSize+1;
end if;
--close record_cursor; --关闭游标，这里还不能关闭，不然java调用不出来
end;
package com.javaoracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Paging {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection c=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "xiaoming", "m123");
CallableStatement cs=c.prepareCall("{call pageing(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 3);
cs.setInt(3, 2);
//注册返回数据
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
//执行调用的过程
cs.execute();
//从注册返回数据
int countRecord=cs.getInt(4);//返回总记录数
int countPages=cs.getInt(5);//返回总页数
ResultSet rs=(ResultSet)cs.getObject(6);
while(rs.next()){
System.out.println("员工号:"+rs.getString(1)+" 姓名:"+rs.getString(2)+" 工资:"+rs.getString(6));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}