oracle分頁(yè)存儲(chǔ)過(guò)程 oracle存儲(chǔ)過(guò)程實(shí)例

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;
   //創(chuàng)建CallableStatement
   cs = conn.prepareCall("{call page.fenye(?,?,?,?,?,?)}");
   cs.setString(1, "mytest");//表名
   cs.setInt(2, 3);//顯示幾條記錄
   cs.setInt(3, 1);//第幾頁(yè)
   //注冊(cè)總記錄數(shù),聲明輸出參數(shù)是什么類型的
   cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
   //注冊(cè)總頁(yè)數(shù)
   cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);
   //注冊(cè)返回的結(jié)果集
   cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);
   cs.execute();
   //取出總記錄數(shù) /這里要注意，getInt(4)中4，是由該參數(shù)的位置決定的
   int rowNum=cs.getInt(4);//獲得輸出參數(shù)
   int pageCount = cs.getInt(5);
   rs=(ResultSet)cs.getObject(6);

   System.out.println("rowNum="+rowNum);
   System.out.println("總頁(yè)數(shù)="+pageCount);
   while (rs.next()) {
    System.out.println("編號(hào)："+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) {
   }
  }
 }
}