SQL 伪列和 DUAL 表 - 它们实际上是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2369142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pseudo columns & DUAL table - what do they actually mean?
提问by Vijay
Dual table is used to select pseudo columns. it has one row and one column DUMMY which has a value X.
双表用于选择伪列。它有一行和一列 DUMMY，其值为 X。
I have two questions
我有两个问题
- What actually does a pseudo column mean?
How is the dual able to give the value for example:
select sysdate from dualwill result in current datetime. How is this possible?
- 伪列实际上是什么意思？
双重如何能够给出价值，例如：
select sysdate from dual将导致当前日期时间。这怎么可能？
回答by APC
A pseudo-column is a function which returns a system generated value. sysdateis a function which returns the current datetime; rownumis a pseudo-column that returns the row number in a result set.
伪列是一个返回系统生成值的函数。 sysdate是一个返回当前日期时间的函数；rownum是一个伪列，返回结果集中的行号。
The nomenclature dates from the earlier days of Oracle, before we had PL/SQL. It just means that we can use these functions in the projection of a SELECT statement, just like the columns of a table. Nowadays we can write our own functions and use them in SQL statements without blinking, and so the phrase "pseudo-column" is a tad confusing.
命名法可以追溯到 Oracle 的早期，在我们拥有 PL/SQL 之前。这只是意味着我们可以在 SELECT 语句的投影中使用这些函数，就像表的列一样。现在我们可以编写自己的函数并在 SQL 语句中使用它们而不会眨眼，所以“伪列”这个短语有点令人困惑。
The feature which distinguishes a function from a pseudo-column is that the pseudo-column returns a different value for each row in the resultsetwhereas a function returns the same value (unless some column in the table is passed as a parameter to derive the value).
将函数与伪列区分开来的特征是，伪列为结果集中的每一行返回不同的值，而函数返回相同的值（除非表中的某些列作为参数传递以导出值）。
Dual is another venerable slice of Oracle history. It is a table which contains one row, and which the database knows contains one row. So the select statement you quote is just saying "give me the current datetime". It is functionally equivalent to
Dual 是 Oracle 历史的另一个令人尊敬的片段。它是一个包含一行的表，数据库知道它包含一行。所以你引用的选择语句只是说“给我当前的日期时间”。它在功能上等同于
select sysdate
from emp
where rownum = 1
/
In PL/SQL the select from dual is nugatory. We can just code this:
在 PL/SQL 中，select from dual 是无效的。我们可以这样编码：
l_date := sysdate;
One common use for DUAL used to be getting the next value of a sequence in a trigger. Since 11g we can do ...
DUAL 的一个常见用途是在触发器中获取序列的下一个值。从 11g 开始，我们可以做...
:new.id := my_seq.nextval;
Under the covers this still executes select my_seq.nextval into :new.id from dual;
在幕后，这仍然执行 select my_seq.nextval into :new.id from dual;
回答by Jonathan Leffler
2. How does selecting from DUAL give the system time?
2.从DUAL中选择如何给系统时间？
SQL has a number of built-in functions which don't need parentheses after them to invoke them. One such function in Oracle is SYSDATE.
SQL 有许多内置函数，它们之后不需要括号来调用它们。Oracle 中的一个这样的函数是 SYSDATE。
Remember, if you have a table, a SELECT statement with no restriction condition (WHERE clause) normally returns one row of data for each row in the table. So, given a table:
请记住，如果您有一个表，那么没有限制条件的 SELECT 语句（WHERE 子句）通常会为表中的每一行返回一行数据。所以，给定一个表：
CREATE TABLE Ex1(Dummy CHAR(10) NOT NULL);
INSERT INTO Ex1 VALUES('Abacus');
INSERT INTO Ex1 VALUES('Sedentary');
INSERT INTO Ex1 VALUES('Caucasus');
Running the SELECT statement:
运行 SELECT 语句：
SELECT Dummy FROM Ex1;
will return 3 rows. Now, suppose I write the statement as:
将返回 3 行。现在，假设我将语句写为：
SELECT 'ABC', Dummy, SYSDATE FROM Ex1;
This will also return 3 rows:
这也将返回 3 行：
- ABC, Abacus, 2010-03-03
- ABC, Sedentary, 2010-03-03
- ABC, Caucasus, 2010-03-03
- ABC, 算盘, 2010-03-03
- ABC, 久坐, 2010-03-03
- ABC，高加索，2010-03-03
If I omit the Dummy column:
如果我省略了 Dummy 列：
SELECT 'ABC', SYSDATE FROM Ex1;
I get:
我得到：
- ABC, 2010-03-03
- ABC, 2010-03-03
- ABC, 2010-03-03
- ABC, 2010-03-03
- ABC, 2010-03-03
- ABC, 2010-03-03
And if I omit the string literal:
如果我省略字符串文字：
SELECT SYSDATE FROM Ex1;
I get:
我得到：
- 2010-03-03
- 2010-03-03
- 2010-03-03
- 2010-03-03
- 2010-03-03
- 2010-03-03
And I delete two rows and rerun the query, I get:
我删除了两行并重新运行查询，我得到：
DELETE FROM Ex1 WHERE Dummy > 'B';
SELECT SYSDATE FROM Ex1;
I get:
我得到：
- 2010-03-03
- 2010-03-03
Because there's just the one row of data in the table Ex1.
因为表 Ex1 中只有一行数据。
Nominally, I could do:
名义上，我可以这样做：
UPDATE Ex1 SET Dummy = 'X';
RENAME TABLE Ex1 AS Dual;
Of course, you can't do that - I'm not sure whether Oracle supports a RENAME TABLE statement and it probably wouldn't let you rename your table so it could be confused with the built-in DUAL table. But conceptually, the table Ex1 with a single row in it is isomorphic with DUAL.
当然，你不能这样做 - 我不确定 Oracle 是否支持 RENAME TABLE 语句，它可能不会让你重命名你的表，所以它可能与内置的 DUAL 表混淆。但从概念上讲，其中只有一行的表 Ex1 与 DUAL 同构。
1. What is a Pseudo-Column?
1. 什么是伪列？
Unless Oracle has a specific special meaning for the term, then a pseudo-column is a column that appears to be part of the table but that is not actually stored as data in the table. A classic example is a row number:
除非 Oracle 对该术语有特定的特殊含义，否则伪列是看起来是表的一部分但实际上并未作为数据存储在表中的列。一个经典的例子是行号：
SELECT ROWNUM, * FROM SomeTable
The output appears to have a column ROWNUM (ROWID in Informix, with which I'm most familiar) but that is not directly stored in the DBMS. Different DBMS have other different pseudo-columns, for different purposes.
输出似乎有一个列 ROWNUM（Informix 中的 ROWID，我最熟悉它）但它没有直接存储在 DBMS 中。不同的 DBMS 有其他不同的伪列，用于不同的目的。
It is sometimes difficult to distinguish between a pseudo-column and a function.
有时很难区分伪列和函数。
回答by Divya Paliwal
A pseudo-column is an Oracle assigned value (pseudo-field) but not stored on disk.
伪列是 Oracle 分配的值（伪字段），但不存储在磁盘上。
Pseudocolumns are not actual columns in a table but they behave like columns.
伪列不是表中的实际列，但它们的行为类似于列。
For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also note that pseudocolumns are allowed in SQL statements, but not in procedural statements.
例如，您可以从伪列中选择值。但是，您不能在伪列中插入、更新或删除。另请注意，SQL 语句中允许使用伪列，但过程语句中不允许。
NOTE:- pseudocolumns are allowed in SQL statements, but not in procedural statements.
注意：- 伪列在 SQL 语句中是允许的，但在过程语句中不允许。
SQL> SELECT sysdate, systimestamp FROM dual; SYSDATE SYSTIMESTAMP
SQL> SELECT sysdate, systimestamp FROM dual; SYSDATE 系统时间戳
13-DEC-07 13-DEC-07 10.02.31.956842 AM +02:00
13-DEC-07 13-DEC-07 10.02.31.956842 上午 +02:00
回答by Mithilesh
pseudo Columns : ROWID , ROWNUM , LEVEL
伪列： ROWID 、 ROWNUM 、 LEVEL
