多次插入SQL oracle [英] Multiple insert SQL oracle
问题描述
当具有标识列时，如何在Oracle 12c中使用SQL进行多次插入?
How do you do multiple insert with SQL in Oracle 12c when you have an identity column?
INSERT ALL
INTO Table1 (Column2) Values (1)
INTO Table1 (Column2) Values (2)
SELECT * FROM dual;
其中Table1具有column1作为标识，将标识列设置为具有与主键约束相同的值.
where Table1 has column1 as an identity, will set the identity column to have the same value which violates the primary key constraint.
CREATE TABLE Table1 (
Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
column2 VARCHAR2(255),
column3 NUMBER,
PRIMARY KEY (Table1Id)
);
INSERT ALL
INTO Table1 (column2, column3) VALUES ('a', '1')
INTO Table1 (column2, column3) VALUES ('b', '2')
SELECT * FROM dual;
--SQL Error: ORA-00001: unique constraint violated
我这是怎么了?
推荐答案
EDIT 添加了两个测试用例以及一个可能的解决方法.
EDIT Added two test cases, and a possible workaround.
尽管Insert语句和insert all语句实际上是相同的常规插入语句.但是当涉及到序列时，它们的工作方式有所不同.
Though Insert statement and insert all statement are practically the same conventional insert statement. But when it comes to sequences, they work differently.
测试案例1:标识列
SQL> DROP TABLE table1 PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE Table1 (
2 Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
3 column3 NUMBER,
4 PRIMARY KEY (Table1Id)
5 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO Table1 (column3) VALUES ('1')
3 INTO Table1 (column3) VALUES ('2')
4 SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.SYS_C0010439) violated
SQL>
让我们看看幕后的实际情况-
Let's see what's actually happening under the hood -
SQL> CREATE TABLE Table1 (
2 Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
3 column3 NUMBER,
4 CONSTRAINT A UNIQUE (Table1Id)
5 );
Table created.
SQL> INSERT ALL
2 INTO Table1 (column3) VALUES (1)
3 INTO Table1 (column3) VALUES (2)
4 SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.A) violated
SQL> SELECT * FROM table1;
no rows selected
SQL> ALTER TABLE table1
2 DISABLE CONSTRAINT a;
Table altered.
SQL> INSERT ALL
2 INTO Table1 (column3) VALUES (1)
3 INTO Table1 (column3) VALUES (2)
4 SELECT * FROM dual;
2 rows created.
SQL> SELECT * FROM table1;
TABLE1ID COLUMN3
---------- ----------
2 1
2 2
SQL>
因此，序列前进到了nextval，但是在我们第一次执行全部插入时出现了唯一的约束冲突.接下来，我们禁用了唯一约束，随后的全部插入"显示该序列并未进行到下一个循环，而是尝试插入重复键.
So, the sequence progressed to nextval however there was an unique constraint violation the first time we did an Insert All. Next, we disabled the unique constraint, and the subsequent Insert All reveals that the sequence did not progress to nextval, rather it attempted to insert duplicate keys.
尽管INSERT-INTO-SELECT语句不会发生此问题.
Though the issue doesn't occur with a INSERT-INTO-SELECT statement.
SQL> INSERT INTO table1(column3) SELECT LEVEL FROM dual CONNECT BY LEVEL <=5;
5 rows created.
SQL>
SQL> SELECT * FROM table1;
TABLE1ID COLUMN3
---------- ----------
2 1
3 2
4 3
5 4
6 5
SQL>
令人惊讶的是，根据元数据，该序列应该自动进行到nextval，但是使用Insert All语句却不会发生.
Surprisingly, as per the metadata, the sequence is supposed to proceed to nextval automatically, however it doesn't happen with an Insert All statement.
SQL> SELECT COLUMN_NAME,
2 IDENTITY_COLUMN,
3 DATA_DEFAULT
4 FROM user_tab_cols
5 WHERE table_name ='TABLE1'
6 AND IDENTITY_COLUMN='YES';
COLUMN_NAME IDENTITY_COLUMN DATA_DEFAULT
--------------- --------------- ------------------------------
TABLE1ID YES "LALIT"."ISEQ$$_94458".nextval
SQL>
测试案例2:明确使用序列
无论使用身份列还是使用显式序列，全部插入的工作方式相同.
The INSERT ALL would work the same way whether an identity column is used or an explicit sequence is used.
SQL> DROP SEQUENCE s;
Sequence dropped.
SQL>
SQL> CREATE SEQUENCE s;
Sequence created.
SQL>
SQL> DROP TABLE t PURGE;
Table dropped.
SQL>
SQL> CREATE TABLE t (
2 ID NUMBER,
3 text VARCHAR2(50),
4 CONSTRAINT id_pk PRIMARY KEY (ID)
5 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO t VALUES (s.nextval, 'a')
3 INTO t VALUES (s.nextval, 'b')
4 INTO t VALUES (s.nextval, 'c')
5 INTO t VALUES (s.nextval, 'd')
6 SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.ID_PK) violated
SQL>
SQL> SELECT * FROM T;
no rows selected
SQL>
SQL> ALTER TABLE t
2 DISABLE CONSTRAINT id_pk;
Table altered.
SQL> INSERT ALL
2 INTO t VALUES (s.nextval, 'a')
3 INTO t VALUES (s.nextval, 'b')
4 INTO t VALUES (s.nextval, 'c')
5 INTO t VALUES (s.nextval, 'd')
6 SELECT * FROM dual;
4 rows created.
SQL> SELECT * FROM T;
ID TEXT
---------- ----------------------------------------
2 a
2 b
2 c
2 d
SQL>
可能的解决方法-使用ROW LEVEL触发器
SQL> CREATE OR REPLACE TRIGGER t_trg
2 BEFORE INSERT ON t
3 FOR EACH ROW
4 WHEN (new.id IS NULL)
5 BEGIN
6 SELECT s.NEXTVAL
7 INTO :new.id
8 FROM dual;
9 END;
10 /
Trigger created.
SQL> truncate table t;
Table truncated.
SQL> INSERT ALL
2 INTO t (text) VALUES ('a')
3 INTO t (text) VALUES ('b')
4 INTO t (text) VALUES ('c')
5 INTO t (text) VALUES ('d')
6 SELECT * FROM dual;
4 rows created.
SQL> SELECT * FROM t;
ID TEXT
---------- -------------------------
3 a
4 b
5 c
6 d
SQL>
这篇关于多次插入SQL oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
