Oracle 标识列并插入到选择中

Question

Oracle 12 引入了一个不错的特性(顺便说一句，早就应该有了!)——标识列。所以这是一个脚本:

CREATE TABLE test (
    a INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    b VARCHAR2(10)
);

-- Ok
INSERT INTO test (b) VALUES ('x');

-- Ok
INSERT INTO test (b)
SELECT 'y' FROM dual;

-- Fails
INSERT INTO test (b)
SELECT 'z' FROM dual UNION ALL SELECT 'zz' FROM DUAL;

前两个插入运行时没有问题，为 1 和 2 的“a”提供值。但是第三个插入失败，出现 ORA-01400: cannot insert NULL into ("DEV"."TEST"."A") 。为什么会这样？错误？关于 identity column restrictions 的文档部分没有提到这样的内容.还是我只是做错了什么？

Answer 1

我相信下面的查询有效，我还没有测试过!

INSERT INTO Test (b)
SELECT * FROM
(
   SELECT 'z' FROM dual
   UNION ALL
   SELECT 'zz' FROM dual
);

不确定，是否对您有任何帮助。

因为，GENERATED ALWAYS AS IDENTITY Oracle 在内部仅使用序列。 general Sequence 上的选项也适用于此。

NEXTVAL 用于获取下一个可用序列，显然它是一个伪列。

以下来自Oracle

您不能在以下结构中使用 CURRVAL 和 NEXTVAL:

• DELETE、SELECT 或 UPDATE 语句中的子查询
• View 或物化 View 的查询
• 带有 DISTINCT 运算符的 SELECT 语句
• 带有 GROUP BY 子句或 ORDER BY 子句的 SELECT 语句
• 一个 SELECT 语句，与另一个 SELECT 语句组合，并设置了 UNION、INTERSECT 或 MINUS运算符(operator)
• SELECT 语句的 WHERE 子句
• CREATE TABLE 或 ALTER TABLE 语句中列的 DEFAULT 值
• CHECK 约束的条件

上面的 subquery 和 SET 操作规则应该可以回答您的问题。

并且由于 NULL 的原因，当 伪列(例如 NEXTVAL)与 SET 操作或上述任何其他规则一起使用时，输出为 NULL，因为 Oracle 无法有效地提取它们组合多个选择。

让我们看看下面的查询，

select rownum from dual
union all 
select rownum from dual

结果是

ROWNUM
1
1