先是创建包,然后创建包体
create or replace package pack1 is
type my_cursor is ref cursor;
v_start number;
v_end number;
v_stmt varchar2(2000);
v_sql varchar2(2000);
procedure fenyePro(v_table in varchar2,numberPerPage in number, pageNo in number,
v_out_result out my_cursor, totalCount out number,pageNumbers out number);
end pack1;
create or replace package body pack1 is
procedure fenyepro (v_table in varchar2,numberPerPage in number, pageNo in number,
v_out_result out my_cursor, totalCount out number,pageNumbers out number) is
begin
--得到每页的起始项和结尾项
v_start:=((pageNo-1)*numberPerPage)+1;
v_end:=pageNo*numberPerPage;
--将分页结果放入游
v_sql:='select t2.userid, t2.userpwd,t2.rights,t2.gender,t2.age,t2.userphone, t2.useraddress, t2.username from (select t1.*,ROWNUM rn from (select * from '||v_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
open v_out_result for v_sql;
--查询总记录数
v_stmt:='select count(*) from '||v_table;
execute immediate v_stmt into totalCount;
if (mod(totalCount,numberPerPage)=0) then
pageNumbers:=totalCount/numberPerPage;
else
pageNumbers:= floor(totalCount/numberPerPage)+1;
end if;
-- dbms_output.put_line('共有'||totalCount||'个'||pageNumbers||'页');
end;
end pack1;
以下是测试代码:
--测试分页的存储过程
declare
v_table varchar2(20):='userTable';
numberperpage number:=3;
pageno number:=2;
Tcount number;
numbers number;
v_out pack1.my_cursor;
tt usertable%rowtype;
begin
pack1.fenyepro(v_table => v_table,
numberperpage => numberperpage,
pageno => pageno,
v_out_result => v_out,
totalcount => Tcount,
pagenumbers => numbers);
loop
fetch v_out into tt;
exit when v_out %notfound;
dbms_output.put_line(tt.username||tt.userpwd||tt.rights||tt.gender);
end loop;
dbms_output.put_line('共有'||Tcount||'个'||numbers||'页');
close v_out;
end;
此时此测试块无法正常运行,因为分页sql语句中会得到比实际表多一行(即rn(rownum))这一行,但是存储过程是可以在java或其他语言中调用,只要你取出想取的几列即可。
若是要测试程序运行成功,除非将存储过程中sql语句写的更详细(把详细的列名写出来对应)。
下面是java 调用代码:
public void splitPage(String tableName,Page page){
getConnection();
try {
cs= con.prepareCall("{call pack1.fenyePro(?,?,?,?,?,?)}");
cs.setString(1, tableName);
cs.setInt(2, page.getNumberPerPage());
cs.setInt(3, page.getPageIndex());
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
cs.execute();
//先把游标拿出来
rs=(ResultSet)cs.getObject(4);
//这两个是存在父类成员变量中供子类使用
totalCount=cs.getInt(5);//这个是要传到page中去计算总页数
System.out.println(totalCount);
totalPageNumbers=cs.getInt(6);//这个是要传到页面上显示出来的
} catch (SQLException e) {
//要是rs为空,即数据库
e.printStackTrace();
}
//这是上一块程序类的子类,然后ResultSet ,totalPageNumbers和totalCount是上个类的成员变量,所以在子程序中可以调用
public Map getPage(String tableName, Page page){
//调用父类用来调用存储过程fenyePro的方法
splitPage(tableName,page);
//用hashmap来存储两个对象
Map<String,Object> hm=new HashMap<String,Object>();
page.setTotalCount(totalCount);//BaseDao成员变量
page.setPageNum(totalPageNumbers);//BaseDao成员变量
hm.put("page", page);
//存放用户列表的数组
ArrayList<User> arr=new ArrayList<User>();
try {
//取出游标封装成用户对象的数组
while(rs.next()){
User user=new User();
user.setUserID(rs.getInt("userID"));
user.setUserName(rs.getString("userName"));
user.setRights(rs.getInt("rights"));
user.setGender(rs.getString("gender"));
user.setAge(rs.getInt("age"));
user.setUserPhone(rs.getLong("userPhone"));
user.setUserAddress(rs.getString("userAddress"));
arr.add(user);
}
hm.put("users", arr);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//关闭资源
this.closeResource();
}
return hm;
}
}