View & DML

Rules for Performing DML Operations on a View

You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudo column ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view

如果创建视图的基表中包含非空约束。
你创建的视图中不包含非空列，那么你不能对这个视图进行插入操作。

 

SQL> create table CUST
  2  (
  3    CUST_ID   NUMBER(2) not null,
  4    CUST_NAME VARCHAR2(15),
  5    E_NAME    LONG
  6  );

Table created

 

SQL> create or replace view  v as select  cust_name from cust;

View created

 

SQL> insert into v values('ORACLE');

insert into v values('ORACLE')

ORA-01400: 无法将 NULL 插入 ("REPORT"."CUST"."CUST_ID")

 

 因为cust_id 定义为非空，在视图中没有包含非空列。所以不能对cust表进行插入操作。

 

 

SQL> create or replace view v as  select cust_id ,cust_name from cust;

View created

SQL> insert into v values(1,'ORACLE');

1 row inserted

 

 

SQL> create  or replace view v_emp as
  2  select * from emp where emp_no = 10  with check option;

View created

 

SQL> update v_emp set emp_no=11 where emp_no=10;

update v_emp set emp_no=11 where emp_no=10

ORA-01402: 视图 WITH CHECK OPTION where 子句违规

 

SQL> create  or replace view v_emp as
  2  select * from emp where emp_no = 10  with read only;

View created

 

SQL> update v_emp set emp_no=11 where emp_no=10;

update v_emp set emp_no=11 where emp_no=10

ORA-42399: 无法对只读视图执行 DML 操作