SQL 插入多行时，语句“select 1 from dual”有什么作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15377140/
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
While inserting multiple rows what does the statement 'select 1 from dual' do?
提问by Y.E.P
While inserting multiple rows into a table using the following style :
使用以下样式将多行插入到表中时：
insert all
into ghazal_current (GhazalName,Rating) values('Ajab Apna Haal Hota Jo Visaal-e-Yaar Hota',5)
into ghazal_current (GhazalName,Rating) values('Apne Hothon Par Sajana Chahta Hun',4)
into ghazal_current (GhazalName,Rating) values('Shaam Se Aankh Mein Nami Si Hai',4)
into ghazal_current (GhazalName,Rating) values('Tumhe Yaad Ho Ke Na Yaad Ho',3)
select 1 from dual;
What does the statement select 1 from dualmean ? What is it here for ?
声明select 1 from dual是什么意思？这是为了什么？
回答by Roney Michael
DUAL is a built-in table, useful because it is guaranteed to return only one row. This means DUALmay be used to get pseudo-columns such as useror sysdate, the results of calculations and the like. The owner of DUAL is SYSbut it can be accessed by every user. DUAL is well-covered in the documentation. Find out more.
DUAL 是一个内置表，很有用，因为它保证只返回一行。该方法DUAL可用于获取诸如user或 之类的伪列sysdate、计算结果等。DUAL 的所有者是SYS但每个用户都可以访问它。DUAL 在文档中有很好的介绍。 了解更多。
In your case, SELECT 1 FROM DUAL;will simply returns 1. You need it because the INSERT ALL syntax demands a SELECT clause but you are not querying the input values from a table.
在您的情况下，SELECT 1 FROM DUAL;将简单地返回1. 您需要它是因为 INSERT ALL 语法需要一个 SELECT 子句，但您不是从表中查询输入值。
回答by pilcrow
Brief re-introduction to one-row tables
单行表的简要重新介绍
Some SQL databases require all values to come FROMa table or table-like object, whereas others permit queries to construct values ex nihilo:
一些 SQL 数据库要求所有值都来自一个表或类似表的对象，而另一些则允许查询构造值ex nihilo：
-- MySQL, sqlite, PostgreSQL, HSQLdb, and many others permit
-- a "naked" select:
SELECT 1;
-- Others *require* a FROM target, like Oracle.
SELECT 1 FROM DUAL;
-- ...and Firebird/Interbase:
SELECT 1 FROM RDB$DATABASE;
-- ...and DB2:
SELECT 1 FROM SYSIBM.SYSDUMMY1;
Here the cardinality of DUAL is important. If it had more than one row, your result set would have more than one row. What happens, for example, when you SELECT 1 FROM A_Table_With_Ten_Rows?
这里 DUAL 的基数很重要。如果它有多于一行，则您的结果集将有多于一行。会发生什么，例如，当你SELECT 1 FROM A_Table_With_Ten_Rows？
Why DUAL is used here
为什么在这里使用 DUAL
The SQL construct VALUES (<row-value-expression>)is a row value constructor. VALUES (1, 2, 3)"creates" a row of values just as SELECT 1, 2, 3does.
SQL 构造VALUES (<row-value-expression>)是一个行值构造函数。 VALUES (1, 2, 3)就像一样“创建”一行值SELECT 1, 2, 3。
Oracle, of course, requires that these values come FROMsomewhere.
甲骨文，当然需要这些值来从某处。
As a demonstration, instead of SELECTing from DUAL at the end of the INSERT ALL, try a table with Nrows, and you'll see that each VALUES()row is inserted Ntimes.
作为演示，不是在 INSERT ALL 结束时从 DUAL 中选择，而是尝试具有N行的表，您将看到每VALUES()行都插入了N次。
回答by Fariba
There are some samples about using dual in Queries:
有一些关于在查询中使用 dual 的示例：
select sysdate from dual /--it returns date of system
SELECT chr(223) FROM dual /--it returns character of Asci?i code
select my_sequence.nextval from dual; /-- It returns the next value of a sequence
select to_char(sysdate,'yyyy/mm/dd','nls_calendar=persian')from dual
/--returns persian date of system
