sql - "ORA-01733: virtual column not allowed here"插入 View 时

Question

我创建了一个名为“view_employee”的 View ，如下所示:

CREATE VIEW view_employee AS
SELECT employee.surname || ', ' || employee.name AS comp_name, employee.sex, sections.name AS section_name, employee_age
FROM sections, employee WHERE employee.section = sections.sect_code;

我想使用 View 将数据插入表中，如下所示:

INSERT INTO view_employee VALUES ('Doe, John', 'm', 'Marketing', 34);

以下是表的列和约束:

create table sections(
  sect_code number(2),
  name varchar2(20),
  income number(5,2)
   constraint CK_sections_income check (income>=0),
  constraint PK_sections primary key (sect_code)
 );

 create table staff(
  ident number(5),
  document char(8),
  sex char(1)
   constraint CK_staff_sex check (sex in ('f','m')),
  surname varchar2(20),
  name varchar2(20),
  address varchar2(30),
  section number(2) not null,
  age number(2)
   constraint CK_staff_age check (age>=0),
  marital_status char(10)
   constraint CK_employee_marital_status check (marital_status in 
('married','divorced','single','widower')),
  joindate date,
   constraint PK_employee primary key (ident),
  constraint FK_employee_section
   foreign key (section)
   references sections(sect_code),
  constraint UQ_staff_document
   unique(document)
);

尝试插入时收到的错误消息如下:

Error starting at Command Line: 1 Column : 1
Error report -
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 -  "virtual column not allowed here"
*Cause:    
*Action:

如何使用 View 将这些值插入表中？提前致谢。

Answer 1

View 不得包含以下任何构造。因此，它可以是可更新的。

集合运算符

一个 DISTINCT 运算符

聚合或分析函数

GROUP BY、ORDER BY、MODEL、CONNECT BY 或 START WITH 子句

SELECT 列表中的集合表达式

SELECT 列表中的子查询

指定为 WITH READ ONLY 的子查询

联接，但有一些异常(exception)，如 Oracle 数据库中所述
管理员指南。