oracle存储过程实现JAVA调用的分页

来源：互联网发布：2011网络春晚翻唱传奇编辑：程序博客网时间：2024/05/16 02:18

1。先建立包中的游标
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
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||'';

Open p_cursor For v_sql;

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

Execute Immediate v_sql Into myrows;
If Mod(myrows,Pagesize)=0 Then
myPageCount:=myrows/Pagesize;
Else
myPageCount:=myrows/Pagesize+1;
End If;
--Close p_cursor;
End;
3。在JAVA程序中获取返回列表的结果集
package com.nyl.ptest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
public class FenyeTest {
public static void main(String[] args) {
  try {

   Class.forName("oracle.jdbc.driver.OracleDriver");

   Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@192.168.13.07:1521:chenjian","iptv_zj","iptv");

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

   cs.setString(1, "emp");
   cs.setInt(2, 5);
   cs.setInt(3, 1);

   cs.registerOutParameter(4, Types.INTEGER);
   cs.registerOutParameter(5, Types.INTEGER);
   cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

   cs.execute();


   int allpages=cs.getInt(4);
   int pagenum=cs.getInt(5);
   System.out.println("allpages:\t"+allpages+"pagenum:\t"+pagenum);

   ResultSet rs=(ResultSet)cs.getObject(6);
   while(rs.next()){
    System.out.println("empno:\t"+rs.getInt(1));
   }


  } catch (Exception e) {
   e.printStackTrace();
  }
}

}