是否只能一一插入带有身份列的甲骨文表?如何使用身份列插入表的多行?
我的Oracle版本是:
Oracle数据库12C标准版本版本12.1.0.2.0-64位生产PL/SQL版本12.1.0.2.0-生产"核心12.1.0.2.0生产"64位Windows的TNS:版本12.1.0.2.0-生产NLSRTL版本12.1.0.2.0-生产
这是我重现此问题的脚本
CREATE TABLE T_BATCH_STATUS
(
ID INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL
, TARGET_DATE TIMESTAMP NOT NULL
, JAVA_CLASS VARCHAR2(100) NOT NULL
, STATUS VARCHAR2(35) NOT NULL
, MSG VARCHAR2(1000)
, FAIL_CNT NUMBER(3) default 0 NOT NULL
, CREATE_USER VARCHAR2(35) NOT NULL
, CREATE_DATE TIMESTAMP NOT NULL
, UPDATE_USER VARCHAR2(35)
, UPDATE_DATE TIMESTAMP
, VERSION number(10) NOT NULL
, CONSTRAINT PK_BATCH_STATUS PRIMARY KEY (ID)
, CONSTRAINT U_BATCH_STATUS UNIQUE (TARGET_DATE, JAVA_CLASS)
)
;
insert into t_batch_status
(
TARGET_DATE
, JAVA_CLASS
, STATUS
, FAIL_CNT
, CREATE_USER
, CREATE_DATE
, VERSION
)
select to_date('2019.01.01', 'yyyy.MM.dd'), 'DownloadAndUnzip', 'NEW', 0, 'SYSTEM', sysdate, 0 from dual
;
1 row inserted
insert into t_batch_status
(
TARGET_DATE
, JAVA_CLASS
, STATUS
, FAIL_CNT
, CREATE_USER
, CREATE_DATE
, VERSION
)
select to_date('2019.02.14', 'yyyy.MM.dd'), 'DownloadAndUnzip', 'PENDING', 0, 'SYSTEM', sysdate, 0 from dual
;
1 row inserted.
但是,如果我全部使用联合来插入相同的2行,则会发生错误:
truncate table t_batch_status;
insert into t_batch_status
(
TARGET_DATE
, JAVA_CLASS
, STATUS
, FAIL_CNT
, CREATE_USER
, CREATE_DATE
, VERSION
)
select to_date('2019.01.01', 'yyyy.MM.dd'), 'DownloadAndUnzip', 'NEW', 0, 'SYSTEM', sysdate, 0 from dual
union all select to_date('2019.02.14', 'yyyy.MM.dd'), 'DownloadAndUnzip', 'PENDING', 0, 'SYSTEM', sysdate, 0 from dual
;
Error report -
SQL Error: ORA-01400: cannot insert NULL into ("CYBERDB_DEV"."T_BATCH_STATUS"."ID")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.
错误使我感到困惑,不是自动生成ID列吗?
在问这个问题后,我尝试了此问题,它起作用,所以它必须是一个oracle错误:
insert into t_batch_status
(
TARGET_DATE
, JAVA_CLASS
, STATUS
, FAIL_CNT
, CREATE_USER
, CREATE_DATE
, VERSION
)
with tmp as (
select b.base + rownum target_date
from all_objects a
, (select to_date('2019.01.01', 'yyyy.MM.dd') base from dual) b
where rownum < 42
)
select target_date, 'DownloadAndUnzip' as JAVA_CLASS , 'NEW' as STATUS
, 0 as FAIL_CNT , 'SYSTEM' as CREATE_USER , sysdate as CREATE_DATE, 0 as VERSION
from tmp
;
41 rows inserted.
它可能是Oracle 12.1中的错误。当我在Oracle 12.2中尝试它时,我收到列模棱两可错误。
如果我对第一个选择查询中的所有列使用适当的别名,则可以正常工作
insert into t_batch_status
(
TARGET_DATE
, JAVA_CLASS
, STATUS
, FAIL_CNT
, CREATE_USER
, CREATE_DATE
, VERSION
)
select to_date('2019.01.01', 'yyyy.MM.dd') as TARGET_DATE ,
'DownloadAndUnzip' as JAVA_CLASS , 'NEW' as STATUS
, 0 as FAIL_CNT , 'SYSTEM' as CREATE_USER , sysdate as CREATE_DATE, 0 as VERSION
from dual
union all
select to_date('2019.02.14', 'yyyy.MM.dd'), 'DownloadAndUnzip', 'PENDING',
0, 'SYSTEM', sysdate, 0 from dual
;