oracle数据库分页

 

oracle数据库分页的正确与标准写法如下三层select

SELECT *FROM

(

SELECT A.*,ROWNUM RN

FROM (SELECT *FROM DOC_TYPE) A

WHERE ROWNUM <=3

)

WHERE RN >=1

oracle数据库是根据伪列进行分页的，

关于伪列，我们应该知道以下几点：

1、oracle数据库使用rownum伪列作为行号，查询行号必须显示写出查询rownum这一列。

2、rownum从1开始，并且rownum的条件只能是<=,而不能是>=，当然>=1除外因为rownum就是从1开始的

例如prod_state有8条记录，显示查询rownum这一列可以知道行号。

select rownum,p.*from prod_state p;

 图 1

查询rownum = 1,有一条记录。

select rownum,p.*from prod_state pwhere rownum =1;

   图 2

查询rownum = 5,没有记录，可以证明rownum从1开始。

select rownum,p.*from prod_state pwhere rownum =5;

                                           图 3

rownum <= 3有三条记录

select rownum,p.*from prod_state pwhere rownum <=3

                                            图 4

rownum >= 3 没有记录

select rownum,p.*from prod_state pwhere rownum >=3;

   图 5

3、行号是不变的，order by排序无法改变每一条记录的行号，

可以通过order by不同的列验证，对比图 6和图 1可以看出

prod_state = 'I'的这条记录在重新排序后到了第二行但是rownum还是8

select rownum,p.*from prod_state porder by p.prod_state_name;

                                       图 6

 

既然如此，那么分页应该怎样写呢？下面开始剖析如何才算是oracle正确的分页查询

 

1、是这样吗，下面图 7是查询1~3条数据库的表记录，看似没有问题

select rownum,p.*from prod_state pwhere rownum <=3and rownum >=1

                                            图 7

然则如果要查询的起始条数如果不是第一条呢？如2~3条，则查询不到记录

这就是rownum从1开始，除>= 1外其它 >= n的条件均是无效的

selectrownum,p.*from prod_state pwhererownum <=3andrownum >=2

                                            图 8

因此，这种写法是错误的！！！

2、rownum >= 2不成受限于rownum是数据库的伪列，那么将rownum连同表记录一起查询出来

并将rownum的值作为结果集的一列，那么这一列就不是伪列了就可以使用 >= 2了

 

图 9查出了2~3两条记录，是想要的

这里给rownum取别名rn区别于查询结果集产生的新的伪列，图 10是加上对结果集伪列的查询。

select *from(

    select rownum rn,p.*from prod_state pwhere rownum <=3)

where rn >=2

                                           图 9

selectrownum,a.*from(

    selectrownum rn,p.*from prod_state pwhererownum <=3) a

where rn>=2

                                            图 10

按道理这种写法已经可以满足分页查询了，但是事实真的如此吗？

比如，我想按prod_state_name这一行升序后取2~3条，应该怎么做呢？

按照图 6中的结果2~3条记录应该是如下

                                                  图 11

那么我们可以写sql了

select *

from(

    select rownum rn,p.*

from prod_state p

    where rownum <=3

    order byp.prod_state_name

)

where rn>=2

这样真的可以吗？答案no,查询结果如下，而并非图 11的两条记录

                                        图 12

因此，这种写法是错误的！！！

3、出现上面的情况就是order by字句并不能改变既有的行号，要想得到正取的结果

就应该最终结果集的行号作为分页的条件

也就是如下的sql

select *

from(select rownum rn,a.*

     from(select *

          fromprod_state p

          orderby p.prod_state_name

         ) a

     where rownum <=3

)where rn >=2

查询结果如下：和图 11一致。。。。。

故oracle分页的正确写法应该如此：

SELECT *FROM

(SELECT A.*,ROWNUM RN

FROM (SELECT *

      FROM DOC_TYPE

     ) A

WHERE ROWNUM <=3

)

WHERE RN >=1

三层select，最里层的select是我们待分页的结果集，

外两层根据结果集的伪列是对结果集的分页。

over~