创建存储过程:
--创建存放游标的包--
create or replace package page_package
is
--申明游标--
type page_cursor is ref cursor;
end page_package;
--创建查询所有用户的过程--
create or replace procedure proc_getAllUser(users out page_package.page_cursor)
as
begin
open users for
select * from tuser;
end;
--创建分页存储过程--
create or replace procedure proc_page(
tableName in varchar2,--需要查询的表名--
pageNum in number,--查询页码--
pageSize in number,--每页条数--
term in varchar,--查询条件,形如: and name = ? and ......--
datas out page_package.page_cursor,--查询出来的数据--
maxPageNum out number --最大页码--
)is
--定义需要执行的sql语句的变量--
v_sql varchar2(1000);
--定义两个整数变量--
v_begin number:=(pageNum-1)*pageSize+1;
v_end number:=pageNum*pageSize;
--定义最大条数的变量--
v_count number:=0;
begin
--给v_sql赋值--
v_sql := 'select p.* from(select t.*,rownum rn from '||tableName||' t where 1=1 '||term||') p where p.rn between '||v_begin||' and '||v_end;
--执行sql给游标赋值--
open datas for v_sql;
--计算最大页码--
v_sql := 'select count(*) from '||tableName||' where 1=1 '||term;
execute immediate v_sql into v_count;
if mod(v_count,pageSize)=0 then
maxPageNum := v_count/pageSize;
else
maxPageNum := v_count/pageSize+1;
end if;
end;
分页sql:
--分页的sql--
select p.* from
(select t.*,rownum rn from torganization t) p where p.rn between 2 and 4
使用:
private ResultSet rs;
private CallableStatement cst;
String sql = "{call proc_page(?,?,?,?,?,?)}";
try {
con = getCon();
cst = con.prepareCall(sql);
cst.setString(1, "torganization");
cst.setInt(2, 1);
cst.setInt(3, 2);
cst.setString(4, " and description like '%是%'");
cst.registerOutParameter(5, oracle.jdbc.OracleTypes.CURSOR);
cst.registerOutParameter(6, oracle.jdbc.OracleTypes.NUMBER);
cst.execute();
int maxPageNum = cst.getInt(6);
rs = (ResultSet)cst.getObject(5);
System.out.println("最大页码是:"+maxPageNum);
System.out.println("数据:");
while(rs.next()){
System.out.println(rs.getString("org_name"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeAll(rs, cst, con);
}