ITPub博客

首页 > 数据库 > Oracle > 分页语句 Bobo Jing

分页语句 Bobo Jing

Oracle 作者:shaojiashuai 时间:2014-02-12 23:18:45 0 删除 编辑

取出sql表中第31到40的记录(以自动增长ID为主键)

sql server方案1:

      select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id

sql server方案2:

      select top 10 * from t where id in (select top 40 id from t order by id) order by id desc

 

mysql方案:select * from t order by id limit 30,10

 

oracle方案:select * from (select rownum r,* from t where r<=40) where r>30

 

pageSize=20;

pageNo = 5;

 

1.分页技术1(直接利用sql语句进行分页,效率最高和最推荐的)

 

mysql:sql = "select * from articles limit " + (pageNo-1)*pageSize + "," + pageSize;

oracle: sql = "select * from " +

                                                "(select rownum r,* from " +

                                                      "(select * from articles order by postime desc)" +

                                                "where rownum<= " + pageNo*pageSize +") tmp " +

                                          "where r>" + (pageNo-1)*pageSize;

注释:第7行保证rownum的顺序是确定的,因为oracle的索引会造成rownum返回不同的值

简洋提示:没有order by时,rownum按顺序输出,一旦有了order by,rownum不按顺序输出了,这说明rownum是排序前的编号。如果对order by从句中的字段建立了索引,那么,rownum也是按顺序输出的,因为这时候生成原始的查询结果集时会参照索引表的顺序来构建。

 

sqlserver:sql = "select top 10 * from id not id(select top " + (pageNo-1)*pageSize + "id from articles)"

 

DataSource ds = new InitialContext().lookup(jndiurl);

Connection cn = ds.getConnection();

//"select * from user where id=?"  --->binary directive

PreparedStatement pstmt = cn.prepareSatement(sql);

ResultSet rs = pstmt.executeQuery()

while(rs.next())

{

      out.println(rs.getString(1));

}

 

2.不可滚动的游标

pageSize=20;

pageNo = 5;

cn = null

stmt = null;

rs = null;

try

{

sqlserver:sql = "select  * from articles";

 

DataSource ds = new InitialContext().lookup(jndiurl);

Connection cn = ds.getConnection();

//"select * from user where id=?"  --->binary directive

PreparedStatement pstmt = cn.prepareSatement(sql);

ResultSet rs = pstmt.executeQuery()

for(int j=0;j<(pageNo-1)*pageSize;j++)

{

      rs.next();

}

 

int i=0;

 

while(rs.next() && i<10)

{

      i++;

      out.println(rs.getString(1));

}

}

cacth(){}

finnaly

{

      if(rs!=null)try{rs.close();}catch(Exception e){}

      if(stm.........

      if(cn............

}

 

3.可滚动的游标

pageSize=20;

pageNo = 5;

cn = null

stmt = null;

rs = null;

try

{

sqlserver:sql = "select  * from articles";

 

DataSource ds = new InitialContext().lookup(jndiurl);

Connection cn = ds.getConnection();

//"select * from user where id=?"  --->binary directive

PreparedStatement pstmt = cn.prepareSatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,...);

//根据上面这行代码的异常SQLFeatureNotSupportedException,就可判断驱动是否支持可滚动游标

 

ResultSet rs = pstmt.executeQuery()

rs.absolute((pageNo-1)*pageSize)

int i=0;

while(rs.next() && i<10)

{

      i++;

      out.println(rs.getString(1));

}

}

cacth(){}

finnaly

{

      if(rs!=null)try{rs.close();}catch(Exception e){}

      if(stm.........

      if(cn............

}

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22568463/viewspace-1116259/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-09-20