Oracle 中的伪列

一、前言

在 Oracle 的表使用过程中，实际上表中还有一些附加的列，称为伪列。伪列就像表中的列一样可以显示出来，但是并不在表中存储。伪列只能用来查询，不能进行增删改操作。

二、ROWID

定义：表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。

作用：通过 ROWID 伪列可以删除重复数据，相同的数据只保留一条。

示例：

--ROWID:物理地址(只有插入了数据才会有伪列：ROWID)
--作用：消除重复数据的，相同的数据只保留一行
SELECT E.EMPNO,
       E.ENAME,
       E.JOB,
       E.MGR,
       E.HIREDATE,
       E.SAL,
       E.COMM,
       E.DEPTNO,
       ROWID
  FROM EMP E;

CREATE TABLE ROWID_TEST(
   ID NUMBER,
   NAME VARCHAR2(10)
);

INSERT INTO ROWID_TEST(ID, NAME) VALUES(1,'aaa');
COMMIT;
INSERT INTO ROWID_TEST(ID, NAME) VALUES(2,'bbb');
COMMIT;

SELECT ID,NAME,ROWID
  FROM ROWID_TEST;


--使用ROWID实现删除表中的重复数据
--查询ROWID最大的那条数据
SELECT T.ID,T.NAME,MAX(ROWID)
  FROM ROWID_TEST T
 GROUP BY T.ID,T.NAME;

--删除ROWID不等于最大值的所有数据
DELETE FROM ROWID_TEST
WHERE ROWID NOT IN(
   SELECT MAX(ROWID)
     FROM ROWID_TEST T
    GROUP BY T.ID,T.NAME
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

三、ROWNUM

定义：在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。

作用：通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

注意点：筛选条件中如果使用到ROWNUM，那么只能用 < 或者 <=来查询，其它的关系运算符返回的都是0行结果。

示例：

--查询EMP表中所有人的ROWID、ROWNUM
SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN FROM EMP E

--查询EMP表中所有人的ROWID、ROWNUM，只取前5条数据
SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN
          FROM EMP E
         WHERE ROWNUM <= 5

--查询EMP表中所有人的ROWID、ROWNUM，只取第5条的数据        
SELECT *
  FROM (SELECT E.EMPNO, E.ENAME, ROWID RI, ROWNUM RN
          FROM EMP E
         WHERE ROWNUM <= 5) T
 WHERE RN = 5;
 
--ROWNUM不能直接取排序后的前N行，需要先进行排序，将排序后的结果作子表，进行取前N行
SELECT T.*, ROWID, ROWNUM
  FROM (SELECT E.EMPNO, E.SAL FROM EMP E ORDER BY E.SAL) T
 WHERE ROWNUM < 3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

四、总结

ROWID与ROWNUM的不同：

1、ROWID是物理地址，ROWNUM是行号；

2、ROWID是插入数据时生成的，而ROWNUM是查询数据时生成的！