数据分页获取（二）
，做完实验还是把它给干掉吧！伤不起...
）“数据分页获取（一）” 的文末提到过，Oracle提供了一个rownum关键字，此关键字集SQL Server中的ROW_NUMBER()函数、Top子句的功能于一身。利用此关键字，可以在 select 子句对结果集进行投影时，为最后的结果集附加一个rownum列（也就是为结果集给出行号）。有了行号，分页就简单了！
由于要让Oracle的存储过程返回结果集并不像SQL Server那样简单，而是有一定复杂度的，所以我还是用最简单的方法——拼接SQL语句来讲解一下rownum关键字在分页查询中的应用（在真实的开发中也可以进行SQL拼接再传入数据库引擎执行，但要注意SQL注入攻击）！先来看看实验结果吧，如下三张截图所示：
ROWID. You can examine a rowid by querying the ROWID pseudocolumn.
Because the ROWID pseudocolumn is not a real column, it does not require database space and cannot be updated, indexed or dropped.
The rowid value persists throughout the life of the table row, but the system can reassign the rowid to a different row after the original row is deleted. Zero is not a valid value for a rowid.
Rowids persists through recovery, backup and restore operations. They do not persist through replication, ttMigrate or ttBulkCp operations.
好了，回到rownum 关键字！rownum 关键字是从1开始，依次递增地为结果集动态指定行号的，而且rownum 关键字的工作时机是在where中列出的其他条件之后，Order by 子句之前（这一点要注意的）。“从1开始，依次递增地为结果集动态指定行号”，这就是为什么不能直接用前面提到的“select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum between (2-1)*6+1 and 2*6”进行分页查询的原因(结果集出来后才生成的行号，已经失去了分页的时机)。你不能让rownum关键字实现从7开始为结果集动态指定行号到12这样的行为，你也不能让rownum关键字实现从2开始为结果集动态指定行号这样的行为（即：select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum>2 也是不行的），因为rownum关键字是从1开始为结果集指定行号的。也就是说你没办法指定rownum关键字为结果集动态指定行号时的起始线（它一直都是从1开始的），下面的语句都是不能通过的：
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum>1;
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum>=2;
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum>5;
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum=10;这种也是不行的，因为rownum关键字是用来动态指定行号的，你不能让它静态的等于某个值。
但是你可以指定rownum关键字结果集动态指定行号时终止线，下面的语句都是能通过的：
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum<6;
select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum<=10;
我们再看到“select T.学号,T.姓名,T.专业 from (select rownum as N, xs.* from xs where xs.专业='信息管理' and rownum<1*6) T where T.N>=((2-1)*6+1) ”，这里外层查询所用的T.N是由内层子查询的select子句投影伪列rownum而来的。经投影后，在内层了查询生成的中间结果中，原来的伪列rownum已经转化为普通的列了，外层查询就可以用此列来做更精确的分页界定。
最后，我们来看几段Oracle官方关于ROWNUM的概述（同样，做IT的应当都能看懂）：
ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which the row was selected. The first row selected has a ROWNUM of 1, the second a ROWNUM of 2, and so on.
Use ROWNUM to limit the number of rows returned by a query as in this example:
The order in which rows are selected depends on the index used and the join order. If you specify an ORDER BY clause, ROWNUM is assigned before sorting. However, the presence of the ORDER BY clause may change the index used and the join order. If the order of selected rows changes, the ROWNUM value associated with each selected row could also change.
For example, the following query may return a different set of employees than the preceding query if a different index is used:
SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;Conditions testing for ROWNUM values greater than a positive integer are always false. For example, the following query returns no rows:
Use ROWNUM to assign unique values to each row of a table. For example:
If your query contains either FIRST NumRows or ROWS m TO n, do not use ROWNUM to restrict the number of rows returned. For example, the following query results in an error message:
