Oracle：插入不带列规范
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6705174/
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
Oracle: insert without columns specification
提问by Zardo
I have an oracle table with a sequence and a trigger to autoincrement a column. Now I want to make an insert. Normally I should write:
我有一个带有序列和触发器的 oracle 表来自动增加一列。现在我想做一个插入。通常我应该写：
INSERT INTO table (column1, column2,...) VALUES (value1, value2)
but I just want to insert a record with no default values. How to do this in Oracle?
但我只想插入一条没有默认值的记录。如何在 Oracle 中执行此操作？
`Mysql`: INSERT INTO table () VALUES ()
`Mssql`: INSERT INTO table default VALUES
`Oracle:` INSERT INTO table (column1, column2,...) VALUES (default, default,...)
Is this the only way? I have to list all columns?
这是唯一的方法吗？我必须列出所有列？
回答by Tony Andrews
INSERT INTO table (column1) VALUES (default);
The rest will be defaulted!
其余的将默认！
回答by Ollie
In Oracle you don't HAVE to specify the columns but not doing so will leave you open to introducing bugs as and when your table definition changes.
在 Oracle 中，您不必指定列，但不这样做会使您在表定义更改时引入错误。
You could insert with:
你可以插入：
INSERT INTO t VALUES (value1, value2, value3);
插入 t 值 (value1, value2, value3);
This assumes the table t has three columns
这假设表 t 有三列
It is far better and supportable to insert with:
使用以下方式插入要好得多且可支持：
INSERT INTO t (column1, column2, column3) VALUES (value1, value2, value3);
INSERT INTO t (column1, column2, column3) VALUES (value1, value2, value3);
I wouldn't use PL/SQL (if you can help it) as you introduce context switching from PL/SQL to SQL and then back to PL/SQL again.
当您引入从 PL/SQL 到 SQL 然后又回到 PL/SQL 的上下文切换时，我不会使用 PL/SQL（如果您能提供帮助的话）。
回答by a_horse_with_no_name
Is this the only way? I have to list all columns?
这是唯一的方法吗？我必须列出所有列？
Yes. And it's good practice to alwaysspecify all columns in an INSERT statement that you want to supply values for.
是的。并且始终在要为其提供值的 INSERT 语句中指定所有列是一种很好的做法。
回答by Dave Costa
I missed this part on the first read:
我在第一次阅读时错过了这一部分：
I have an oracle table with a sequence and a trigger to autoincrement a column.
我有一个带有序列和触发器的 oracle 表来自动增加一列。
So I assume there is on PK column populated using the sequence, and the others all have default values. Given that, I would do this:
所以我假设有使用序列填充的 PK 列，而其他列都有默认值。鉴于此，我会这样做：
INSERT INTO table (pk_column) VALUES (NULL);
The trigger will override the NULL value (and if it doesn't for some reason, the insert will fail); and the other columns will be populated using defaults.
触发器将覆盖 NULL 值（如果由于某种原因没有覆盖，插入将失败）；其他列将使用默认值填充。
回答by nanosoft
There is only onecase where you don't need to specify COLUMNS in Oracle and that is when you are inserting rows with values specified for all columns and that too in same sequence as the definition of table. In all other cases one needs to specify column list.
目前只有一个，你不需要在Oracle中指定柱的情况下，这是当你将与所有列指定值的行和太在同一序列表的定义。在所有其他情况下，需要指定列列表。
Example :
例子 ：
create table tt (id number, name varchar2(30));
insert into tt values (1,'AC');
insert into tt values ('2',1);
Especially note 2nd insert statement -> First parameter '2' is provided as String and expectation is number in ID column. Here does necessary casting and inserts 2 in ID column. But user may have wanted to insert '2' in NAME column and 1 as ID. With specifying column list Oracles helps us to avoid such errors.
特别注意第二条插入语句 -> 第一个参数“2”作为字符串提供，期望是 ID 列中的数字。这里进行必要的铸造并在 ID 列中插入 2。但是用户可能想在 NAME 列中插入 '2' 和 1 作为 ID。通过指定列列表 Oracles 帮助我们避免此类错误。
Also there will be rarely any situation now a days where you DON"T need to specify column list because almost all tables created mostly have an auto-incremented unique ID column which is auto-filled via Trigger or because of IDENTITY columnsand you provide values of rest of the columns only that means those column list must be provided else it will result in error.
此外，现在很少有任何情况您不需要指定列列表，因为几乎所有创建的表都具有自动递增的唯一 ID 列，该列是通过触发器自动填充的，或者因为IDENTITY 列而您提供值其余的列仅意味着必须提供这些列列表，否则会导致错误。
In case this unique ID is generated and inserted by an application say generated in java application via Random number generation or Hibernate then as all column would be inserted column list might be avoided.
如果此唯一 ID 是由应用程序生成并插入的，例如通过随机数生成或Hibernate在 java 应用程序中生成的，那么可能会避免插入所有列的列列表。
