oracle Oracle中按顺序插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31968093/
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
Inserting multiple rows with sequence in Oracle
提问by Alex Poole
This is the query i have used for insert multiple rows in oracle database. But when am using sequence within it it raises error as ORA-00001: unique constraint. How to do it.
这是我用于在 oracle 数据库中插入多行的查询。但是当我在其中使用序列时，它会引发错误为 ORA-00001：唯一约束。怎么做。
INSERT ALL
INTO POSTAL_CODE( postal_code,desc)
VALUES(postal_code.NEXTVAL,'Coimbatore')
INTO POSTAL_CODE (postal_code,desc)
VALUES(postal_code.NEXTVAL,'Mumbai') SELECT * FROM DUAL;
回答by Alex Poole
The restrictions on multitable insertsinclude:
- You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.
- 不能在多表插入语句的任何部分指定序列。多表插入被视为单个 SQL 语句。因此，对 NEXTVAL 的第一个引用生成下一个数字，并且语句中的所有后续引用都返回相同的数字。
That isn't quite true - you can use a sequence, it just always gets the same value, so it can be useful to create parent and child records in one go by referring to the same sequence.
这并不完全正确 - 您可以使用序列，它总是获得相同的值，因此通过引用相同的序列一次性创建父记录和子记录可能很有用。
If you want to continue to use insert allyou couldwork around that by using a non-deterministic function that gets the sequence value:
如果您想继续使用，insert all您可以通过使用获取序列值的非确定性函数来解决这个问题：
CREATE FUNCTION get_seq RETURN NUMBER IS
BEGIN
RETURN postal_code_seq.nextval;
END;
/
INSERT ALL
INTO POSTAL_CODE( postal_code,description)
VALUES(get_seq,'Coimbatore')
INTO POSTAL_CODE (postal_code,description)
VALUES(get_seq,'Mumbai') SELECT * FROM DUAL;
2 rows inserted.
SELECT * FROM postal_code;
POSTAL_CODE DESCRIPTION
--------------------------------------- --------------------
1 Coimbatore
2 Mumbai
But that's a bit awkward. You're probably better off using individual insert statements - using a multitable insert into a single table isn't really gaining you much anyway - or a trigger to set the unique column from the sequence, or a CTE/inline view to generate the values to insert.
但这有点尴尬。您可能最好使用单独的插入语句 - 使用多表插入到单个表中并没有真正让您受益 - 或者从序列中设置唯一列的触发器，或者 CTE/内联视图来生成值插入。
回答by David Aldridge
I would use a before insert trigger to populate the key column (if no value has been supplied by the insert) instead of this method. Sequences do not work well with multi-table insert.
我会使用一个 before insert 触发器来填充键列（如果插入没有提供值）而不是这个方法。序列不适用于多表插入。
回答by Jon Heller
Instead of modifying your database objects you can simply rewrite the multitable INSERT ALLinto a single INSERTwith multiple rows concatenated by UNION ALL:
您可以简单地将多表重写INSERT ALL为一个INSERT多行，而不是修改您的数据库对象UNION ALL：
INSERT INTO postal_code
SELECT postal_code_seq.NEXTVAL, description
FROM
(
SELECT 'Coimbatore' description FROM dual UNION ALL
SELECT 'Mumbai' description FROM dual
);
Note that the sequence must be called in an outer query. Using a sequence in the inner query looks like it could simplify the code but would raise the error "ORA-02287: sequence number not allowed here".
请注意，必须在外部查询中调用该序列。在内部查询中使用序列看起来可以简化代码，但会引发错误“ORA-02287：此处不允许使用序列号”。
回答by Kalpesh Kikani
try to insert this multiple line in oracle database
尝试在 oracle 数据库中插入多行
INTO POSTAL_CODE (postal_code,desc)
VALUES(&postal_code,&desc) SELECT * FROM DUAL;
回答by Kalpesh Kikani
INSERT ALL INTO POSTAL_CODE( postal_code,desc) VALUES(postal_code.NEXTVAL,&desc) INTO POSTAL_CODE (postal_code,desc) VALUES(postal_code.NEXTVAL,&desc) SELECT * FROM DUAL;
INSERT ALL INTO POSTAL_CODE(postal_code,desc) VALUES(postal_code.NEXTVAL,&desc) INTO POSTAL_CODE (postal_code,desc) VALUES(postal_code.NEXTVAL,&desc) SELECT * FROM DUAL;
