oracle rownum分頁與顯示記錄小測

  同事問及關於rownum表記錄不顯示問題,經查閱官方手冊,附上測試筆記:

SQL> insert into t_rownum select level from dual connect by level<=5;
 
5 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from t_rownum;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                      4
                                      5
 
SQL> select * from t_rownum where rownum>0;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                      4
                                      5
 
SQL> select * from t_rownum where rownum>=0;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                      4
                                      5
 
SQL> select * from t_rownum where rownum>=1;
 
                                      A
---------------------------------------
                                      1
                                      2
                                      3
                                      4
                                      5
 
SQL> select * from t_rownum where rownum>=2;
 
                                      A
---------------------------------------
 
SQL> select * from t_rownum where rownum>2;
 
                                      A
---------------------------------------
 
SQL>

 

官方手冊源語:

 

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
  FROM employees
  WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
  SET column1 = ROWNUM;

   總而述之:oracle會對提取的記錄一一比較是否符合where條件,故不會顯示記錄