Oracle分頁語句介紹和編寫分頁的預存程序

--Oracle分頁語句介紹
--為每條記錄編號
select t1.*,rownum rn from (select * from emp) t1;
--取出前10條記錄
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
--取出第6條到第10條記錄，此語句可以作為分頁的一個模板
select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--開始編寫分頁的過程
--1、先建立一個包，包中定義類型test_cursor,是個遊標
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
--2、編寫分頁的預存程序
create or replace procedure fenye
(tableName in varchar2,
pageSize in number,--每頁最多可以顯示的記錄條數
pageNow in number,--當前是第幾頁
myRows out number,--查詢出的記錄總共有多少條
myPageCount out number,--查詢出的記錄總共可以分多少頁
p_cursor out testpackage.test_cursor--返回查詢結果的記錄集
) is
--定義部分
--定義sql語句
v_sql varchar2(1000);
--定義兩個整數並賦值
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
--執行部分
--並對員工工資按由高到低排列
v_sql:='select * from (select t1.*,rownum rn from (select * from '|| tableName
 ||' order by sal) t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
open p_cursor for v_sql;
--計算myRows和myPageCount
--重新給v_sql賦值
v_sql:='select count(*) from '||tableName;
--執行sql，並把返回的值付給myRows
execute immediate v_sql into myRows;
--計算myPageCount
if mod(myRows,pageSize)=0 then
myPageCount:=myRows/pageSize;
else
myPageCount:=myRows/pageSize+1;
end if;
--關閉遊標
--close p_cursor;
end;

--使用java代碼調用這個預存程序

package test;
import java.sql.*;
public class FenYe{
public static void main(String[] args){
try{
//載入資料庫驅動
Class.forName("oracle.jdbc.driver.OracleDriver");
//擷取資料庫連接
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","tiger");
//建立CallableStatement對象
CallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
//給前三個輸入參數的？賦值
cs.setString(1,"emp");//要查詢的是emp表
cs.setInt(2,5);//每頁顯示5條記錄
cs.setInt(3,1);//先顯示第一頁
//註冊總記錄數
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
//註冊總頁數
cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
//註冊結果集
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
//執行預存程序
cs.execute();

//接收返回的總記錄數
int rowNum = cs.getInt(4);
//接收返回的總頁數
int pageCount = cs.getInt(5);
//接收返回的結果集
ResultSet rs = (ResultSet)cs.getObject(6);

//列印輸出結果
System.out.println("總記錄數：" + rowNum);
System.out.println("總頁數：" + pageCount);
System.out.println("==========每頁顯示" +5 + "條===========");
System.out.println("==========當前是第" +1 + "頁===========");
int i = 1;
while(rs.next()){
System.out.println("(" + (i++) +  ")編號：" + rs.getInt(1) + "，姓名：" + rs.getString(2) + "，  工資：" + rs.getInt(6));
}
}catch(Exception e){
e.printStackTrace();
}
}
}

需要說明的是上面的代碼只是為了簡單示範一下調用資料庫中的分頁預存程序，所以對資料庫資源的串連和關閉作最佳化(甚至沒有關閉串連，關閉結果集等)。

實際開發中是必須關閉和釋放資料庫資源的。