同事问及关于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条件,故不会显示记录