插入带有标识列的Oracle表时如何使用％ROWTYPE? [英] How to use %ROWTYPE when inserting into Oracle table with identity column?

问题描述

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

I have an Oracle 12c database with a table containing an identity column:

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

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

Now I want to insert into the table using PL/SQL. Since in practice the table has many columns, I use %ROWTYPE:

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

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

However, it give me this error:

ORA-32795:无法插入到生成的始终标识列中
ORA-06512:在第5行

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

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

Since it is very good for code readability and maintainability, I do not want to stop using %ROWTYPE. Since I under no circumstances want to allow anything but the automatically generated ID's I do not want to lift the GENERATED ALWAYS restriction.

此文章建议，能够使用%ROWTYPE的唯一方法是切换到GENERATED BY DEFAULT ON NULL.没有其他方法可以解决此问题吗?

This article suggests that the only way to be able to use %ROWTYPE is to switch to GENERATED BY DEFAULT ON NULL. Is there no other way to fix this?

推荐答案

您可以创建视图并在其中插入:

You can create a view and insert there:

CREATE OR REPLACE VIEW V_FOO AS
SELECT BAR -- all columns apart from virtual columns
FROM foo;

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

这篇关于插入带有标识列的Oracle表时如何使用％ROWTYPE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！