oracle分頁存儲過程

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class TestPage {

public TestPage() {

}

public static void main(String[] args) {

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@localhost:1521:xmgl";

String username = "xmgl";

String password = "xmgl";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, username, password);

System.out.println("ok");

CallableStatement cs = null;

//建立CallableStatement

cs = conn.prepareCall("{call page.fenye(?,?,?,?,?,?)}");

cs.setString(1, "mytest");//表名

cs.setInt(2, 3);//顯示幾條記錄

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();

//取出總記錄數 /這裡要注意，getInt(4)中4，是由該參數的位置決定的

int rowNum=cs.getInt(4);//獲得輸出參數

int pageCount = cs.getInt(5);

rs=(ResultSet)cs.getObject(6);

System.out.println("rowNum="+rowNum);

System.out.println("總頁數="+pageCount);

while (rs.next()) {

System.out.println("編号："+rs.getInt(1)+" 名字："+rs.getString(2)+" 密碼："+rs.getString(3));

}

} catch (SQLException ex2) {

ex2.printStackTrace();

} catch (Exception ex2) {

ex2.printStackTrace();

} finally {

try {

if (rs != null) {

rs.close();

if (stmt != null) {

stmt.close();

}

if (conn != null) {

conn.close();

}

}

} catch (SQLException ex1) {

}

}

}

}

存儲語句：

create or replace package page is

type test_cursor is ref cursor;

procedure fenye(tableName in varchar2,pageSize in number,pageNow in number,myRows out number,myPageCount out number,p_cursor out page.test_cursor);

end;

create or replace package body page is

procedure fenye(

tableName in varchar2,

pageSize in number,--一頁顯示記錄數

pageNow in number,--第幾頁

myRows out number,--總記錄數

myPageCount out number,--總頁數

p_cursor out page.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||') t1 where rownum<='||v_end||') where rn>='||v_begin||' order by userid asc ';

--把遊标和sql 關聯

open p_cursor for v_sql;

--計算myRows 和myPageCount

--組織一個sql 語句

v_sql:='select count(*) from '||tableName;

--執行sql,并把傳回的值，賦給myRows；

execute immediate v_sql into myRows;

--計算myPageCount

--if myrows%Pagesize=0 then 這樣寫是錯的

if mod(myRows,pageSize)=0 then

myPageCount:=myrows/pageSize;

else

myPageCount:=myrows/pageSize+1;

end if;

--關閉遊标

--close p_cursor;

end;

end;