转Oracle分页查询

Oracle分页查询

 

1.Oralce的分页查询

分页查询:可以简化表复杂度,让一张很大的表,分成很多块,不要一次性全部显示成一整块;方便阅览

 

可以将下列语句当成一个模版使用select * from (select t1.*,rownum rn from emp t1 where rownum < 8) where rn > 3;

 

定义一个包,包里面有游标变量类型:(记住游标变量是不能在包中或包体中声明的比如 mycursor sys_refcursor这是不允许的)

 

 

create or replace package mypage
is
type cursor_emp is ref cursor;
end mypage;

 

接着写存储过程,来实现分页的业务逻辑

   下面定义了六个变量, 三个输入参数(表名,第几页,一页显示几行数据),三个输出变量(表中数据总共的行数,表总共分了几页,游标变量(用来指明你需要查询的记录)).

 

 

--pageCount 显示第几页
--pageSize 一页显示的数据
create or replace procedure emp_pro(tableName varchar2,pageCount number,pageSize number,
allSize out number,allPage out number,mycursor out mypage.cursor_emp)
is
Sql_s varchar2(1000);
Sql_count varchar2(1000);
startSize number:=pageCount*pageSize-pageSize+1;
endSize number:=pageCount*pageSize;
begin

  Sql_s:='select * from
    (select t1.*,rownum rn from '||tableName||' t1 where rownum <= '||endSize||') where rn >= '||startSize;
    open mycursor for Sql_s;
   Sql_count:='select count(*) from emp';
   execute immediate Sql_count into allSize;
   if mod(allSize,pageSize)=0 then
           allPage:=allSize/pageSize;
      else
           allPage:=allSize/pageSize+1;
   end if;
end;

 

    我们来分析下面的语句

Sql_s:='select * from(select t1.*,rownum rn from '||tableName||' t1 where rownum <= '||endSize||') where rn >= '||startSize; open mycursor for Sql_s;

 

    其实在PL/SQL编程中,可以把你需要写的SQL语句给一个字符变量,当执行存储过程的时候,oracle自动会辨认出来,

   execute immediate SQL语句 into 变量

   这一句的意思是:立即执行给定的SQL语句,把返回的结果给变量

 

  JAVA调用存储过程:

 

 

try
{
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Class","scott","admin");
    CallableStatement call = con.prepareCall("{call emp_pro(?,?,?,?,?,?)}");
    call.setString(1, "EMP");
    call.setInt(2, 5);
    call.setInt(3, 4);
    //注册属性值
    call.registerOutParameter(4, OracleTypes.INTEGER);
    call.registerOutParameter(5, OracleTypes.INTEGER);
    call.registerOutParameter(6, OracleTypes.CURSOR);
    call.execute();
    int ALLSIZE = call.getInt(4);
    int ALLPAGE = call.getInt(5);
    System.out.println("记录数: "+ALLSIZE);
    System.out.println("总页数: "+ALLPAGE);
    ResultSet rs = (ResultSet)call.getObject(6);
    while(rs.next())
    {
        System.out.println("EMPNO="+rs.getInt(1)+"  "+"ENAME="+rs.getString(2)+
                " "+"SAL="+rs.getInt(6));
    }
}
catch(Exception e)
{
    e.printStackTrace();
}

 

转自：http://aazham.iteye.com/blog/1167341