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 :='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?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. }
