oracle存储过程分页代码

oracle存储过程分页代码

/*******存储过程分页代码**********/--包头create or replace package pck_my istype c_my is ref cursor;procedure page_moed(  v_table in varchar2, --表名  current_page in out number,--当前页  pageSize in out number,--页行数  total out number,--总行数  countPage out number,--总页数  c_cursor out pck_my.c_my--游标  );end pck_my;--bodycreate or replace package body pck_my asprocedure page_moed(  v_table in varchar2,  current_page in out number,  pageSize in out number,  total out number,  countPage out number,  c_cursor out pck_my.c_my  )is v_sql varchar2(1000);v_max number;v_min number;e_table exception;begin  --判断参数if v_table is null then  raise e_table;  --return;  end if;if current_page is null thencurrent_page:=1;end if;if pageSize<=0 thenpageSize:=5;end if; --计算 最大行 最小行v_max:=(current_page+1)*pageSize;v_min:=current_page*pageSize;--获取数据v_sql:= 'select *  from (select filminfo.*, rownum as t from '|| v_table ||' where rownum <='|| v_max||') where t > ' ||v_min;open c_cursor for v_sql;--计算总行数v_sql:='select count(*)  from '|| v_table;execute immediate v_sql into total;--计算总页数if mod(total,pageSize)=0 then  countPage:=total/pageSize;else  countPage:=total/pageSize+1;end if;--exceptionexception  when e_table then   dbms_output.put_line('表名不能为空');end;end pck_my;--  exetselect * from filminfo

java测试代码

package com.rui;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;public class Pckage {/** * @param args */public static void main(String[] args) {Connection con;ResultSet rs;CallableStatement cs;try {Class.forName("oracle.jdbc.driver.OracleDriver");con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ABC","tenement","rui");String sql="{call pck_my.page_moed(?,?,?,?,?,?)}";cs=con.prepareCall(sql);//指定类型/* v_table in varchar2,  current_page in out number,  pageSize in out number,  total out number,  countPage out number,  c_cursor out pck_my.c_my*///cs.setString(1, null);cs.setString(1, "filminfo");cs.setInt(2, 3);cs.setInt(3,5);cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);cs.execute();int total=cs.getInt(4);//总行数int countPage=cs.getInt(5);//总页数rs=(ResultSet)cs.getObject(6);//resultSystem.out.println("总行数:"+total+"\t总页数"+countPage);System.out.println("------------------------------------");while(rs.next()){   System.out.println("FILMNAME:"+rs.getString("FILMNAME")+"\tFILMID:"+rs.getInt("FILMID"));}} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}