oracle学习分页存储过程和jdbc调用

分页查询

一．由于oracle和mysql，SqlServer不同，在分页的过程也比较麻烦。

有一个部门表dept。

1.      要查询前三行可以使用rownum做限制。

select * from dept where rownum <=3--查询前三条

2.      还可以查询排序好的前三行。

select * from (select * from dept  order by dno asc) where rownum<=3--排好序后，查询前三条

3.      如果要查询第二个前三行就比较麻烦了，rownum对大于限制没有作用。可以这样写

select dno,dname from(select dno,dname,rownum as rowno fromdept order by dno asc)where rowno>3 and rowno<=6;--获得第二个前三行

 二．在pl/sql编程中。

可以创建存储过程来实现分页查询的功能。创建存储过程需要两个输入变量，当前页数curPage和页面大小pageSize。基本算法是：每次查询的行数需要大于当前页数减一乘以页面大小，小于和等于点前页数和页面大小的乘积。即：rownum>(curPage-1)*pageSize and rownum<=curpage*pageSize;

代码：

create or replace procedureproc_Page(in_curpage in number,in_pagesize in number)is

v_m number :=(in_curpage-1)*in_pagesize;

v_n number :=in_pagesize*in_curpage;

cursor c_page is select t1.* from(selectdept.*,rownum rn from dept where rownum <=v_n) t1 where rn >v_m;

begin

  fortemp in c_page loop  

   dbms_output.put_line('编号：'||temp.dno||'    姓名：'||temp.dname);

   end loop;

end; 

--Sql窗口执行 

begin 

proc_Page(2,2);

commit;

end; 

--命令窗口执行

SQL> set serveroutput on--打开开关显示，默认为off

SQL> exec proc_Page(2,2)--执行sql 

三．在java中调用存储过程实现分页。 

先创建包来存储游标，然后在java程序中读取游标。

--通过自定义包来引入游标的定义

create or replace package myPackage as

      type c_page is ref cursor;

end myPackage;

--分页存储过程，参数有表名，当前页数，页面大小，输出游标，输出总页数。

create or replace procedurepro_Page(in_tableName varchar2,

     in_curPage number,in_pageSize number,

     out_page out myPackage.c_page,out_totalPage out number) is

v_m number := (in_curPage-1)*in_pageSize;

v_n number := in_curPage * in_pageSize;

v_sql varchar2(200);

v_count number;

begin

 v_sql := 'select t2.* from (select t1.*,rownum rn from (select * from'||in_tableName||')t1

        where rownum <=:v2) t2 where rn>:v2';

 open out_page for v_sql using v_n,v_m; --in_tableName不能写到using中否则报错

-- close out_page; --不能在这里关闭，否则调用时候报无效的ref游标

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

 execute immediate v_sql into v_count;

  ifmod(v_count,in_pageSize)=0 then

  out_totalPage := v_count/in_pageSize;

  else

  out_totalPage := v_count/in_pageSize +1;

  endif;

end pro_Page; 

jdbc中调用过程：

public class Oracle {

         publicstatic void main(String[] args) throws Exception {

                   //1.加载驱动  

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

                   //2.得到连接

Connection con =DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","fuhao", "fuhao123456");

                   CallableStatementcs=con.prepareCall("call pro_Page(?,?,?,?,?)");

                   cs.setString(1,"t_student"); //表名

                   cs.setInt(2,2); //cur_page

                   cs.setInt(3,3);//pageSize

                   //注册输入参数 类型是游标类型

                  cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);

                   //输出参数 当前一共有多少页

                   cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

                   cs.execute();//提交后可以得到返回值

                   inttotalPage = cs.getInt(5);

                   ResultSetrs = (ResultSet)cs.getObject(4);

                   System.out.println("一共有"+totalPage+"页");

                   while(rs.next()){

                            intid =rs.getInt("studentID");

                            Stringname=rs.getString("studentName");

                          System.out.println("ID:"+id+"   Name:" +name);

                 }

         }

}