SQL 如何使用序列值将多行插入到 oracle 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/228221/
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
How can I insert multiple rows into oracle with a sequence value?
提问by Ovesh
I know that I can insert multiple rows using a single statement, if I use the syntax in this answer.
我知道我可以使用单个语句插入多行，如果我使用这个答案中的语法。
However, one of the values I am inserting is taken from a sequence, i.e.
但是，我插入的值之一取自一个序列，即
insert into TABLE_NAME
(COL1,COL2)
select MY_SEQ.nextval,'some value' from dual
union all
select MY_SEQ.nextval,'another value' from dual
;
If I try to run it, I get an ORA-02287 error. Is there any way around this, or should I just use a lot of INSERT statements?
如果我尝试运行它，则会收到 ORA-02287 错误。有什么办法可以解决这个问题，还是我应该使用很多 INSERT 语句？
EDIT:
If I have to specify column names for all other columns other than the sequence, I lose the original brevity, so it's just not worth it. In that case I'll just use multiple INSERT statements.
编辑：
如果我必须为序列以外的所有其他列指定列名，我会失去原来的简洁性，所以它只是不值得。在这种情况下，我将只使用多个 INSERT 语句。
采纳答案by WW.
This works:
这有效：
insert into TABLE_NAME (COL1,COL2)
select my_seq.nextval, a
from
(SELECT 'SOME VALUE' as a FROM DUAL
UNION ALL
SELECT 'ANOTHER VALUE' FROM DUAL)
回答by Dilshod Tadjibaev
It does not work because sequence does not work in following scenarios:
它不起作用，因为序列在以下情况下不起作用：
- In a WHERE clause
- In a GROUP BY or ORDER BY clause
- In a DISTINCT clause
- Along with a UNION or INTERSECT or MINUS
- In a sub-query
- 在 WHERE 子句中
- 在 GROUP BY 或 ORDER BY 子句中
- 在 DISTINCT 子句中
- 连同 UNION 或 INTERSECT 或 MINUS
- 在子查询中
Source: http://www.orafaq.com/wiki/ORA-02287
来源：http: //www.orafaq.com/wiki/ORA-02287
However this does work:
但是这确实有效：
insert into table_name
(col1, col2)
select my_seq.nextval, inner_view.*
from (select 'some value' someval
from dual
union all
select 'another value' someval
from dual) inner_view;
Try it out:
试试看：
create table table_name(col1 varchar2(100), col2 varchar2(100));
create sequence vcert.my_seq
start with 1
increment by 1
minvalue 0;
select * from table_name;
回答by EvilTeach
insert into TABLE_NAME
(COL1,COL2)
WITH
data AS
(
select 'some value' x from dual
union all
select 'another value' x from dual
)
SELECT my_seq.NEXTVAL, x
FROM data
;
I think that is what you want, but i don't have access to oracle to test it right now.
我认为这就是你想要的，但我现在无法访问 oracle 来测试它。
回答by billjamesdev
From Oracle Wiki, error 02287 is
从Oracle Wiki 中，错误 02287 是
An ORA-02287 occurs when you use a sequence where it is not allowed.
当您使用不允许的序列时，会发生 ORA-02287。
Of the places where sequences can'tbe used, you seem to be trying:
在无法使用序列的地方，您似乎正在尝试：
In a sub-query
在子查询中
So it seems you can't do multiples in the same statement.
所以看起来你不能在同一个语句中做多个。
The solution they offer is:
他们提供的解决方案是：
If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column
如果您希望为创建的每一行将序列值插入到列中，则创建一个前插入触发器并在触发器中获取序列值并将其分配给列
回答by Brian Schmitt
A possibility is to create a trigger on insert to add in the correct sequence number.
一种可能性是在插入时创建触发器以添加正确的序列号。
回答by Mordred
this works and there is no need to use union all.
这是有效的，没有必要使用 union all。
Insert into BARCODECHANGEHISTORY (IDENTIFIER,MESSAGETYPE,FORMERBARCODE,NEWBARCODE,REPLACEMENTDATETIME,OPERATORID,REASON)
select SEQ_BARCODECHANGEHISTORY.nextval, MESSAGETYPE, FORMERBARCODE, NEWBARCODE, REPLACEMENTDATETIME, OPERATORID, REASON
from (
SELECT
'BAR' MESSAGETYPE,
'1234567890' FORMERBARCODE,
'1234567899' NEWBARCODE,
to_timestamp('20/07/12','DD/MM/RR HH24:MI:SSXFF') REPLACEMENTDATETIME,
'PIMATD' OPERATORID,
'CORRECTION' REASON
FROM dual
);
