Oracle,分页,存储过程三个词结合起来,来个综合点的小练习,运用之前的PL/SQL创建一个分页的存储过程,只需要简单几步即可。
1.声明一个引用游标
[sql] view plaincopyprint?01.create or replace package cur_ref_type
02.as-- is or as can be used
03.type cur_ref is ref cursor;
04.end;
create or replace package cur_ref_type
as-- is or as can be used
type cur_ref is ref cursor;
end;2.编写存储过程
[sql] view plaincopyprint?01.create or replace procedure divideByPge
02. (tableName varchar2,pageSize number,currentPage number,
03. totalPage out number,totalRecord out number,results out cur_ref_type.cur_ref)--要带包名
04.is
05. v_sql varchar2(200);--用于写sql语句的
06. v_begin number;--开始位置
07. v_end number;--结束位置
08.begin
09. v_begin := (currentPage -1) * pageSize;
10. v_end := currentPage * pageSize;
11. v_sql :='select count(*) from '||tableName;--将表名字符串拼接sql语句
12. execute immediate v_sql into totalRecord;--查询所有记录
13. totalPage :=ceil(totalRecord/pageSize);--计算总页数
14. v_sql :=&#39;select * from (select rownum rn,&#39;||tableName||&#39;.* from &#39;||tableName||&#39; where rownum <= &#39;||v_end||&#39;) where rn > &#39;||v_begin;
15. open results for v_sql;
16.end;
create or replace procedure divideByPge
(tableName varchar2,pageSize number,currentPage number,
totalPage out number,totalRecord out number,results out cur_ref_type.cur_ref)--要带包名
is
v_sql varchar2(200);--用于写sql语句的
v_begin number;--开始位置
v_end number;--结束位置
begin
v_begin := (currentPage -1) * pageSize;
v_end := currentPage * pageSize;
v_sql :=&#39;select count(*) from &#39;||tableName;--将表名字符串拼接sql语句
execute immediate v_sql into totalRecord;--查询所有记录
totalPage :=ceil(totalRecord/pageSize);--计算总页数
v_sql :=&#39;select * from (select rownum rn,&#39;||tableName||&#39;.* from &#39;||tableName||&#39; where rownum <= &#39;||v_end||&#39;) where rn > &#39;||v_begin;
open results for v_sql;
end;3.Java客户端调用
[java] view plaincopyprint?01.String driver ="oracle.jdbc.driver.OracleDriver";
02. String url = "jdbc:oracle:thin:@localhost:1521:orcl";
03. String username = "scott";
04. String password = "ysjian";
05. try {
06. Class.forName(driver);
07. Connection cOnn=
08. DriverManager.getConnection(url,username,password);
09. CallableStatement cs = conn.prepareCall("{call
10. divideByPge(?,?,?,?,?,?)}");
11. cs.setString(1,"emp");//表名
12. cs.setInt(2, 3);//分页单位
13. cs.setInt(3, 1);//当前页
14. cs.registerOutParameter(4, OracleTypes.INTEGER);//注册输出参数
15. cs.registerOutParameter(5, OracleTypes.INTEGER);
16. cs.registerOutParameter(6, OracleTypes.CURSOR);//注册输出参数游标
17. cs.execute();//执行
18. ResultSet rs = (ResultSet)cs.getObject(6);//获得游标
19. while(rs.next()){
20. int empno = rs.getInt("empno");
21. String ename = rs.getString("ename");
22. System.out.println(empno+"-->"+ename);
23. }
24. } catch (ClassNotFoundException | SQLException e) {
25. e.printStackTrace();
26. }