Code:
- create or replace package fenyebao as --包
- type f_cursor is ref cursor;
- end fenyebao;
- create or replace procedure fenye
- (tablename in varchar2,
- pagesize1 in number,
- pagenow in number,
- myrows out number,--总数
- mypagecount out number,--总页数
- p_cursor out fenyebao.f_cursor--返回的记录集
- )
- is
- v_sql varchar2(1000);
- v_begin number:=(pagenow-1)*pagesize1+1;
- v_end number:=pagenow*pagesize1;
- begin
- v_sql:='select * from (select t1.*,rownum rn from (select * from '||tablename
- ||')t1 where rownum<'||v_end||') where rn>'||v_begin;
- --把游标和sql关联
- open p_cursor for v_sql;
- v_sql:='select count(*) from '||tablename;
- execute immediate v_sql into myrows;--执行sql并把返回值赋给myrows
- if mod(myrows,pagesize1)=0 then
- mypagecount:=myrows/pagesize1;
- else
- mypagecount:=myrows/pagesize1+1;
- end if;
- end;
Code:
- import java.sql.*;
- public class fenye {
- public static void main(String[] args)throws Exception{
- Class.forName("oracle.jdbc.driver.OracleDriver");
- Connection con = null;
- con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
- CallableStatement cst = con.prepareCall("{call fenye(?,?,?,?,?,?)}");
- cst.setString(1, "employees");
- cst.setInt(2, 5);
- cst.setInt(3, 2);
- cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
- cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
- cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
- cst.execute();
- int rownum = cst.getInt(4);
- int pageCount = cst.getInt(5);
- ResultSet rs = (ResultSet)cst.getObject(6);
- System.out.println(rownum);
- System.out.println(pageCount);
- while(rs.next()){
- System.out.println("姓名:"+rs.getString(2)+" email:"+rs.getString(3));
- }
- }
- }