Oracle 伪列总结

1. 伪列定义与常用伪列

1.1 定义

Oracle官方文档的 Oracle Database SQL Language Reference 11g Release 2 (11.2) E41084-02 对伪劣的定义:

A pseudocolumn (pseudo, 虚假的) behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
A pseudocolumn is also similar to a function without arguments . However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

---

伪列 类似于表中的列，但是它并不存实际保存在表中。你可以对其进行 查询操作 ，但是你却不能对其进行 增加、修改或者是删除;

一个伪列也类似于一个没有参数的函数。但是，没有参数的函数通常在结果集中为每一列返回相同的结果，伪列通常 为每一列返回不同的值。

---

1.2 常用伪列

ROWNUM 和 ROWID 是常用的 Oracle 伪列, 开始演示:

1.2.1 ROWNUM

1.2.1.1 情景 1

需求: 查找2到10范围内的记录（这里包括2和10的记录）

-- 数据来自 Oracle 中 scott 用户中的演示表
SELECT * FROM 
    (SELECT e.*, ROWNUM rn, FROM emp e) a
WHERE a.rn BETWEEN 2 AND 10;

点击下载 scott 演示表

1.2.1.2 情景 2

需求: 查找前 5 行的记录

-- 方法一: 直接通过 ROWNUM 查询
SELECT * FROM emp WHERE ROWNUM < 5;

-- 方法二: 通过子表查询
SELECT * FROM 
    (SELECT e.*, ROWNUM rn FROM emp e) a
WHERE a.rn < 5; 

1.2.1.3 情景 3 (情景 2 升级)

需求: 查找 2 到 5 行的记录

-- 错误方法演示: 
SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5;
-- 请注意, 若是直接使用 ROWNUM 当做 WHERE 子句判断条件的话, 
-- 其判等的条件必须包含初始值 1;
-- 请看后面的解答 --> ^_^ 

-- 正确方法: 
SELECT * FROM 
    (SELECT e.*, ROWNUM rn FROM emp e) a
WHERE a.rn BETWEEN 2 AND 5;

1.2.1.4 解答: 为什么不能直接在 WHERE 子句中, 使用: ROWNUM BETWEEN 2 AND 5 ?

-- 以下是所有错误范例
-- 错误 1: 
SELECT * FROM emp WHERE ROWNUM BETWEEN 2 AND 5;

-- 错误 2: 
SELECT * FROM emp WHERE ROWNUM >= 2 AND ROWNUM <= 5;

很多同学, 在这里就比较迷糊了, 为什么多加了如下条件:

ROWNUM >=2 

就会出错了; 这个要从 ROWNUM 的定义查找原因:

ROWNUM 是根据 SQL 查询出的结果给 每行分配一个逻辑编号, 每行数据会因为输出的顺序不同而获得不同的逻辑编号 (因为顺序而改变);

如果有 ROWNUM >= 2 条件, 则当查询第一行数据 (第一行数据的 ROWNUM 值此时为 1 )的时候, 不符合条件, 第一行被过滤掉; 那么此时, 再继续查询第二行数据的时候, 此时第二行数据的 ROWNUM 值依然是 1, 任然不符合条件;

所以, 使用 ROWNUM >=2 不会报语法错误, 只是所有的结果都被过滤掉了, 自然就没有结果显示.

1.2.1.5 ROWNUM 更多应用

其实 ROWNUM 的应用方面有很多, 比如刚刚展示的 分页功能, 若要深入了解 ROWNUM 详细功能, 请看 Oracle 伪列 ROWNUM 详解

1.2.2 ROWID

主要应用于有重复记录的表, 因为 ROWID 具有唯一性, 所以查询的效率很高;

1.2.2.1 情景 1

需求: student 表中的 name 字段会有重复的情况，但是学生的学号是不会重复的; 现在, 如果只保留姓名重复的学生中, 最后加入的那一位, 该如何实现?

DELETE FROM stu WHERE ROWID <> (
    SELECT MAX(ROWID) FROM stu);

1.2.3 ROWNUM 和 ROWID 比较

ROWNUM 和 ROWID 都是伪列，但是生成方式不同;

ROWNUM 是根据 SQL 查询出的结果给每行分配一个逻辑编号, 每行数据会因为输出的顺序不同而获得不同的逻辑编号 (因为顺序而改变);

ROWID 是物理结构上的，在每条记录 INSERT 到数据库中时，都会有一个唯一的物理记录 （不会变）;

2. 参考文档

1. Oracle 伪列
2. oracle中rownum和rowid的区别
3. Oracle 伪列: ROWNUM 应用与总结