Oracle伪列窥探深层原理（oracle伪列概念）

Oracle伪列：窥探深层原理！

Oracle是目前世界上使用最广泛的关系型数据库管理系统之一。在使用Oracle数据库时，经常会遇到一些伪列的概念，例如ROWNUM、ROWID等。本文将介绍什么是Oracle伪列，以及它们的原理和用法。

一、什么是Oracle伪列？

Oracle伪列指一些在Oracle数据库中重要而特殊的列，它们不是普通表中存储的列，而是由Oracle自动生成的特殊列。伪列不能被修改、删除，也不占用表的存储空间，但能够参与表的查询、排序、分组等操作。

Oracle内置了许多伪列，例如：ROWNUM、ROWID、CURRVAL、NEXTVAL等。这些伪列提供了很多便利，在编写SQL语句时常常用到。

二、Oracle伪列原理

1.ROWNUM

ROWNUM是Oracle伪列中最常用的一个。它是个序列值，表示查询出的记录在结果集中的行号。ROWNUM是一种递增序列，其值会在查询结果集中自动产生，从1开始依次递增。在执行SELECT语句时可以用ROWNUM对查询结果进行限制，从而控制查询结果的范围和数目。

示例代码：

“`sql

SELECT *

FROM

(

SELECT t.*, ROWNUM r

FROM emp t

WHERE rownum

)

WHERE r > 5;

上面的SQL语句查询了表emp中的前10条记录，然后再从这10条记录中筛选出第6-10条记录。其中在子查询中添加了一个ROWNUM伪列，用来表示每条记录在子查询结果中的行号。

2.ROWID

ROWID是Oracle伪列中另一个常用的列，它唯一标识了一行数据存放在数据块中的地址，可以被用来快速定位一条记录。ROWID是由数据库系统自动生成的，具有唯一性，不能被修改。ROWID伪列可用于DELETE、UPDATE等操作，但在查询中使用ROWID伪列会大大降低查询效率。

示例代码：

```sql
SELECT *
FROM emp
WHERE ROWID = 'AAAEEnAAEAAAACLAAD';

上面的SQL语句查询ROWID为“AAAEEnAAEAAAACLAAD”这一行数据的详细信息。

3.CURRVAL / NEXTVAL

CURRVAL是Oracle序列的伪列，它表示当前序列值（上次请求序列返回的值）。使用CURRVAL必须满足以下条件：

① 必须是SELECT语句。

② 必须使用序列号。

示例代码：

“`sql

SELECT my_sequence.CURRVAL

FROM DUAL;

NEXTVAL是Oracle序列的伪列，它表示下一个序列值。使用NEXTVAL必须满足以下条件：

① 必须是INSERT语句（或者以等价物的形式）。

② 必须使用序列号。

示例代码：

```sql
INSERT INTO my_table(id, value)
VALUES(my_sequence.NEXTVAL, 'test');

以上代码用来向my_table表中插入一条记录，并将id赋值为当前my_sequence的下一个值。

三、Oracle伪列用法

1.使用ORDER BY对ROWNUM进行排序

在查询时通常需要使用ORDER BY进行排序，但如果在查询时使用ROWNUM伪列作为排序依据，会发现排序结果并不符合预期。这是因为Oracle会先选出ROWNUM小于等于指定数目的若干行数据，然后对其进行排序，最后在结果集中返回指定数目的记录，而不是按照指定数目的记录进行排序。

为了解决这个问题，需要先使用ROWNUM作为一个子查询的列，然后在外部查询中使用ORDER BY对子查询中的ROWNUM进行排序。

示例代码：

“`sql

SELECT *

FROM

(

SELECT t.*, ROWNUM r

FROM emp t

WHERE rownum

ORDER BY hiredate

)

WHERE r > 5;

2.使用ROWID进行更新操作

ROWID伪列与UPDATE语句的结合可以实现快速高效的更新操作。

示例代码：

```sql
UPDATE emp
SET sal = sal * 1.1
WHERE ROWID = 'AAAEEnAAEAAAACLAAD';

以上SQL语句将ROWID为“AAAEEnAAEAAAACLAAD”的记录中的sal字段值增加了10%。

3.使用序列CURRVAL / NEXTVAL生成ID

序列是Oracle数据库中的一种对象，它可以用来生成唯一的ID。通过使用CURRVAL和NEXTVAL伪列，可以快速并对序列进行操作。

示例代码：

“`sql

— 创建序列

CREATE SEQUENCE my_sequence

INCREMENT BY 1

START WITH 1

NO CACHE

NOCYCLE;

— 获取当前序列ID值

SELECT my_sequence.CURRVAL

FROM DUAL;

— 插入一条记录并使用序列值作为ID

INSERT INTO my_table(id, value)

VALUES(my_sequence.NEXTVAL, ‘test’);

以上代码中，首先使用CREATE SEQUENCE创建了一个名为my_sequence的序列，然后使用SELECT my_sequence.CURRVAL语句获取当前序列ID值，最后利用INSERT INTO语句向my_table表中插入一条记录，并将id赋值为当前my_sequence的下一个值。

四、总结

Oracle伪列是Oracle数据库中的一个重要概念，它们可以提供很多便利，在编写SQL语句时经常用到。本文介绍了Oracle伪列的含义、原理以及使用方法，希望对读者有所帮助。