如何使用ROWNUM是个老生常谈的问题了，本来没有打算专门强调这个问题，但是最近在看Oracle的官方PL/SQL文档时发现了一个严重的错误，借这个机会还是简单说一下。
首先来看Oracle文档的描述，在10.2的PL/SQL文档中，Oracle关于PL/SQL中直接使用SELECT的查询描述为：
Selecting At Most One ROW: SELECT INTO Statement IF you expect a query TO ONLY RETURN one ROW, you can WRITE a regular SQL SELECT statement WITH an additional INTO clause specifying the PL/SQL variable TO hold the RESULT. IF the query might RETURN more than one ROW, but you do NOT care about VALUES after the FIRST, you can RESTRICT any RESULT SET TO a single ROW BY comparing the ROWNUM VALUE. IF the query might RETURN no ROWS at ALL, USE an exception handler TO specify any actions TO take WHEN no DATA IS found. |
这个描述是没有问题的，但是到了11.2中，文档的描述变成了：
Single-ROW RESULT Sets IF you expect the query TO RETURN ONLY one ROW, THEN USE the SELECT INTO statement TO store VALUES FROM that ROW IN either one OR more scalar VARIABLES (see "Assigning Values to Variables with the SELECT INTO Statement") OR one record variable (see "SELECT INTO Statement for Assigning Row to Record Variable"). IF the query might RETURN multiple ROWS, but you care about ONLY the nth ROW, THEN RESTRICT the RESULT SET TO that ROW WITH the clause WHERE ROWNUM=n. FOR more information about the ROWNUM pseudocolumn, see Oracle DATABASE SQL LANGUAGE Reference. |
第一个反应是不是我看错了，居然可以通过WHERE ROWNUM = N来限制只返回第N条记录 ，再仔细看了一遍，并和10g的文档对比，发现11.2和10.2中的不同。于是第二个反应是Oracle在11.2中提供了新特性，使得PL/SQL语句中直接SELECT可以通过WHERE ROWNUM来直接控制游标，于是特意在11.2上进行了测试，发现结果和10.2上没有区别，ROWNUM = N是行不通的，除非N等于1。
SQL> SELECT * FROM v$version; BANNER ------------------------------------------------------------------------------ Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS FOR 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> SELECT * FROM tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- COMPANIES TABLE MLOG$_T_F TABLE MLOG$_T_P TABLE MV_T_ORACLE TABLE SERVICES TABLE SERVICE_RATES TABLE SERVICE_USAGE TABLE SERVICE_USERS TABLE T TABLE T_DEFER TABLE T_F TABLE T_LOAD_LOB TABLE T_P TABLE T_PART TABLE 已选择14行。 SQL> SET serverout ON SQL> DECLARE 2 v_name varchar2(30); 3 BEGIN 4 SELECT tname INTO v_name FROM tab WHERE rownum = 5; 5 dbms_output.put_line(v_name); 6 exception 7 WHEN no_data_found THEN 8 dbms_output.put_line('rownum equal the num bigger than 1 is incorrect!'); 9 END; 10 / rownum equal the num bigger than 1 IS incorrect! PL/SQL 过程已成功完成。 |
显然Oracle文档这里出现了严重的错误，如果要使用ROWNUM来控制返回第几行结果，那么至少需要2层嵌套查询才可以。
最后简单总结一下ROWNUM，很多人都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。但是却并不了解造成这种限制条件的机制是什么。
其实ROWNUM的返回很简单，ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。
理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，仍然是1，因此所有的记录都不满足条件。
了解了原理，就可以很容易的写出ROWNUM大于某值的例子：
SQL> SELECT * FROM tab WHERE rownum = 1 OR rownum > 1; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- COMPANIES TABLE MLOG$_T_F TABLE MLOG$_T_P TABLE MV_T_ORACLE TABLE SERVICES TABLE SERVICE_RATES TABLE SERVICE_USAGE TABLE SERVICE_USERS TABLE T TABLE T_DEFER TABLE T_F TABLE T_LOAD_LOB TABLE T_P TABLE T_PART TABLE 已选择14行。 |