oracle INSERT ALL INTO 和 Sequence.nextval 用于代理键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8370283/
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
INSERT ALL INTO and Sequence.nextval for a Surrogate Key
提问by Robert
I'm trying to insert 40 rows using an INSERT ALL INTO and I'm not certain on how to insert the surrogate key. Here's what I have
我正在尝试使用 INSERT ALL INTO 插入 40 行，但我不确定如何插入代理键。这是我所拥有的
BEGIN
INSERT ALL
INTO question(question_id)
VALUES (question_seq.nextval)
END
Now if I add another INTO VALUES then I get a unique constraint violation.
现在，如果我添加另一个 INTO VALUES，那么我会得到一个唯一的约束违规。
BEGIN
INSERT ALL
INTO question(question_id)
VALUES (question_seq.nextval)
INTO question(question_id)
VALUES (question_seq.nextval)
END
How can I update the sequences nextval value for each INTO VALUES so that I can avoid the unique constraint violation? I assumed that nextval would automatically update itself.
如何更新每个 INTO VALUES 的序列 nextval 值，以便我可以避免唯一约束违规？我认为 nextval 会自动更新自己。
UPDATE: I don't know if this is the best way to handle this but here's the solution I came up with:
更新：我不知道这是否是处理此问题的最佳方法，但这是我想出的解决方案：
first I created a function that returns a value then I called that function in the id field of the VALUES clause
首先，我创建了一个返回值的函数，然后在 VALUES 子句的 id 字段中调用了该函数
create or replace
FUNCTION GET_QUESTION_ID RETURN NUMBER AS
num NUMBER;
BEGIN
SELECT UHCL_QUESTIONS_SEQ.nextval
INTO num
FROM dual;
return num;
END GET_QUESTION_ID;
INSERT ALL
INTO question(question_id)
VALUES (GET_QUESTION_ID())
INTO question(question_id)
VALUES (GET_QUESTION_ID())
采纳答案by Jon Heller
You can use something like this:
你可以使用这样的东西：
insert into question(question_id)
select question_seq.nextval from
(
select level from dual connect by level <= 40
);
Although it's not a very convenient format, especially if there are other columns you want to add. You'd probably need to create another UNION ALLquery, and join it by the LEVELor ROWNUM.
尽管它不是一种非常方便的格式，尤其是当您要添加其他列时。您可能需要创建另一个UNION ALL查询，并通过LEVELor加入它ROWNUM。
My first thought was to do something like this:
我的第一个想法是做这样的事情：
insert into question(question_id)
select question_seq.nextval value from dual
union all
select question_seq.nextval from dual;
But it generates ORA-02287: sequence number not allowed here, due to the restrictions on sequence values.
但ORA-02287: sequence number not allowed here由于序列值的限制，它会生成。
By the way, are you sure your INSERT ALLworks without a subquery? I get the error ORA-00928: missing SELECT keyword, and the diagram from the 11.2 manualimplies there must be a subquery:
顺便说一句，你确定你的INSERT ALL作品没有子查询吗？我收到错误ORA-00928: missing SELECT keyword，11.2手册中的图表暗示必须有一个子查询：
回答by Nick.McDermaid
Being from a SQL Server background, I've always created a trigger on the table to basically emulate IDENTITY functionality. Once the trigger is on, the SK is automatically generated from the sequence just like identity and you don't have to worry about it.
由于具有 SQL Server 背景，我总是在表上创建一个触发器来基本上模拟 IDENTITY 功能。一旦触发器打开，SK 就像身份一样从序列中自动生成，您不必担心。
回答by Robert
I don't know if this is the best way to handle this but here's the solution I came up with:
我不知道这是否是处理此问题的最佳方法，但这是我想出的解决方案：
first I created a function that returns a value then I called that function in the id field of the VALUES clause
首先，我创建了一个返回值的函数，然后在 VALUES 子句的 id 字段中调用了该函数
create or replace
FUNCTION GET_QUESTION_ID RETURN NUMBER AS
num NUMBER;
BEGIN
SELECT UHCL_QUESTIONS_SEQ.nextval
INTO num
FROM dual;
return num;
END GET_QUESTION_ID;
INSERT ALL
INTO question(question_id)
VALUES (GET_QUESTION_ID())
INTO question(question_id)
VALUES (GET_QUESTION_ID())
