key words :Oracle分页 视图
google了一下关于Oracle的分页方法，方法还不少，大多数效果差不多-有点恶心. 恶心也要作，不过后来就是大家都用得这种方式在我这里出现了新问题，奇怪的是怎么没有别人碰到?
String condition = " teacher_id = " + userId + " and school_id="+siteId;
sql =
" select * " +
" from your_table where " + condition +
" and rowid not in ( select rowid from your_table where" + condition +
" and rownum <= " + (pageIndex - 1) * Constants.PAGE_NUMS + ") " +
" and rownum <= " + Constants.PAGE_NUMS ;
现在的问题是我需要按照table的某个字段排序，于是改成如下:
String condition = " teacher_id = " + userId + " and school_id="+siteId;
sql =
" select * " +
" from your_table where " + condition +
" and rowid not in ( select rowid from your_table where" + condition +
" and rownum <= " + (pageIndex - 1) * Constants.PAGE_NUMS + "order by id desc) " +
" and rownum <= " + Constants.PAGE_NUMS + " order by id desc";
这个sql有问题么?
答案是可能有问题，也可能没有问题，因为据说在8i的Oracle版本之前都不行，实际上也不尽然，在我的9i和10g我得到的是同样的错误 "
missing right parenthesis",还有一位兄弟估计是DBA建议我去metalink打一个patch,埃，动作太大了，不敢动。
问题还是要解决，试了下类似于select a.*,rownum r from (select * from table where ...) a where rownum < 10 等的方法，效果一样，就是不能加嵌套的order by
最后，用视图的方法间接解决问题，因为我要解决的问题实际就是按某个字段排序，那么在视图里先对table进行排序，再在视图的基础上作操作就OK了.
另，还有一种不错的实现方法，即用OracleCachedRowSet,分页也比较简单，有点类似于hibernate,由于时间关系没有时间去看，感兴趣的朋友可以看一下.
BTW: 对于视图可能rowid有问题，可以改成视图的某个主键替换
String condition = " teacher_id = " + userId + " and school_id="+siteId;
sql =
" select * " +
" from your_table where " + condition +
" and id not in ( select id from your_table where" + condition +
" and rownum <= " + (pageIndex - 1) * Constants.PAGE_NUMS + "order by id desc) " +
" and rownum <= " + Constants.PAGE_NUMS + " order by id desc";