create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--开始编写分页的过程
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
--执行部分--order by empno
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;
--计算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;
--关闭游标,但是游标是不可关闭的，不然你在java调用的时候就会出错，closed cursor
--close p_cursor;
end;
/*
import java.sql.*;
import java.util.Scanner;
public class TestPlSql12 {
public static void main(String[] args) {
try{
Scanner sc = new Scanner(System.in);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","tiger");
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
System.out.println("请输入表名");
String tablename = sc.next();
cs.setString(1, tablename);
System.out.println("请输入一页显示的记录数");
int pagesize = sc.nextInt();
cs.setInt(2, pagesize);
System.out.println("请输入当前页数，显示相应内容");
int pagenow = sc.nextInt();
cs.setInt(3, pagenow);
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);
while(rs.next()){
System.out.println("编号"+rs.getInt(1)+"名字"+rs.getString(2)+"工资"+rs.getFloat(6));
}
}catch(Exception ee){
ee.printStackTrace();
}finally{
}
}
}
*/
