Oracle实践--PL/SQL综合之分页存储过程

Oracle PL/SQL分页的存储过程

         Oracle，分页，存储过程三个词结合起来，来个综合点的小练习，运用之前的PL/SQL创建一个分页的存储过程，只需要简单几步即可。

1.声明一个引用游标

[sql] view plaincopyprint?

1. create or replace package cur_ref_type 
2. as-- is or as can be used 
3. type cur_ref is ref cursor; 
4. 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?

1. create or replace procedure divideByPge 
2.          (tableName varchar2,pageSize number,currentPage number, 
3.                  totalPage out number,totalRecord out number,results out cur_ref_type.cur_ref)--要带包名 
4. is 
5. v_sql varchar2(200);--用于写sql语句的 
6. v_begin number;--开始位置 
7. v_end number;--结束位置 
8. begin 
9. 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 :='select * from (select rownum rn,'||tableName||'.* from '||tableName||' where rownum <= '||v_end||') where rn > '||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 :='select count(*) from '||tableName;--将表名字符串拼接sql语句
 execute immediate v_sql into totalRecord;--查询所有记录
 totalPage :=ceil(totalRecord/pageSize);--计算总页数
 v_sql :='select * from (select rownum rn,'||tableName||'.* from '||tableName||' where rownum <= '||v_end||') where rn > '||v_begin;
 open  results for v_sql;
end;

3.Java 客户端调用

[java] view plaincopyprint?

1. String driver ="oracle.jdbc.driver.OracleDriver"; 
2.         String url  = "jdbc:oracle:thin:@localhost:1521:orcl"; 
3.         String username = "scott"; 
4.         String password = "ysjian"; 
5.         try { 
6.            Class.forName(driver); 
7.            Connection conn = 
8.                         DriverManager.getConnection(url,username,password); 
9.            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.         }