oracle - 插入带有标识列的 Oracle 表时如何使用 %ROWTYPE？

Question

我有一个 Oracle 12c 数据库，其中有一个包含标识列的表:

CREATE TABLE foo (
  id   NUMBER  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  bar  NUMBER
)

现在我想使用 PL/SQL 插入到表中。由于实际上该表有很多列，因此我使用 %ROWTYPE:

DECLARE
  x foo%ROWTYPE;
BEGIN
  x.bar := 3;
  INSERT INTO foo VALUES x;
END;

但是，它给了我这个错误:

ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 5

由于它对于代码的可读性和可维护性非常好，所以我不想停止使用%ROWTYPE。由于我在任何情况下都不想允许除自动生成的 ID 之外的任何内容，所以我不想解除 GENERATED ALWAYS 限制。

This文章建议使用 %ROWTYPE 的唯一方法是切换到 GENERATED BY DEFAULT ON NULL。没有其他办法可以解决这个问题吗？

Answer 1

由于您使用的是 12c，所以我唯一能想到的就是使标识列INVISIBLE，如下面的代码。

问题在于，它使得获取带有 id 的 %ROWTYPE 变得更加困难，但这是可行的。

当然，使用您的表的其他人也可能会因为看不到主键而感到困惑!

我不认为我会这样做，但它是对你的问题的回答，无论其值(value)如何。

DROP TABLE t;

CREATE TABLE t ( id number invisible generated always as identity, 
                 val varchar2(30));

insert into t (val) values ('A');                 

DECLARE

  record_without_id t%rowtype;
  CURSOR c_with_id IS SELECT t.id, t.* FROM t;
  record_with_id c_with_id%rowtype;

BEGIN
  record_without_id.val := 'C';
  INSERT INTO t VALUES record_without_id;

  -- If you want ID, you must select it explicitly
  SELECT id, t.* INTO record_with_id FROM t WHERE rownum = 1;

  DBMS_OUTPUT.PUT_LINE(record_with_id.id || ', ' || record_with_id.val);
END;
/

SELECT id, val FROM t;