/*
案例:存储过程完成分页查询
*/
--分页语句 :select * from (select a.*, rownum rn from (select * from emp) a where rownum <= 10) where rn >= 5;
--创建一个包
create or replace package pak_Paging is
type paging_cursor_type is ref cursor;
end;
--创建存储过程 pro_Paging(表名,每页记录数,显示第几页的页数,总记录数,总页数,游标)
create or replace procedure pro_Paging(tableName in varchar2, pagingSize in number, pageNum in number, rowSum out number, pageCount out number, paging_cursor out pak_Paging.paging_cursor_type) is
--定义部分
--定义sql语句
v_sql varchar2(1000);
--定义两个整数,开始记录数和结束记录
v_begin number := (pageNum - 1) * pagingSize + 1;
v_end number := pageNum * pagingSize ;
begin
--执行部分
v_sql := 'select * from (select a.*, rownum rn from (select * from '|| tableName ||') a where rownum <= '|| v_end ||') where rn >= '|| v_begin ||'';
--打开游标,关联sql语句
open paging_cursor for v_sql;
--计算总记录数 和 总页数
v_sql := 'select count(*) from ' || tableName;
--执行sql,并将返回值赋值给rowSum
execute immediate v_sql into rowSum;
--计算PageCount
if mod(rowSum, pagingSize)=0 then
pageCount := rowSum/pagingSize;
else
pageCount := rowSum/pagingSize + 1;
end if;
--关闭游标
--close paging_cursor;
end;
/* Java 程序
public class TestPagingProcedure {
public static void main(String[] args) {
Connection conn = null;
CallableStatement cs = null;
String tableName = "emp";
//分页每页显示的记录数
int pageSize = 5;
//页数
int pageNum = 3;
int rowSum, pageCount;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获得连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "dog", "dog");
cs = conn.prepareCall("{call PRO_PAGING(?, ?, ?, ?, ?, ? )}");
cs.setString(1, tableName);
cs.setInt(2, pageSize);
cs.setInt(3, pageNum);
//返回结果 参数设置
//总记录数
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
//总页数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
//结果集
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
//得到结果
rowSum = cs.getInt(4);
pageCount = cs.getInt(5);
System.out.println("总记录数: " + rowSum + " ---- 总页数: " + pageCount);
ResultSet rs = (ResultSet) cs.getObject(6);
while (rs.next()) {
System.out.println("员工号:" + rs.getInt(1) + " 员工姓名:" + rs.getString(2) + " 职位:" + rs.getString(3) + " 领导工号:" + rs.getString(4) + " 入职日期:" + rs.getString(5) + " 工资:" + rs.getString(6) + " 奖金:" + rs.getString(7) + " 部门号:" + rs.getString(8));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
cs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
*/
转载于:https://my.oschina.net/u/1757476/blog/495399