oracle 插入数据限制,SQL入门之8 限制插入数据的范围

insertinto 中使用内联视图[@more@]

-- A subquery is used to identify the table and columns of the DML statement.

SQL> insert into (select empno,ename from emp

2 where deptno=20

3 with check option)

4 values (333,'wangwang');

insert into (select empno,ename from emp

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> 1

1* insert into (select empno,ename from emp

SQL> ed

Wrote file afiedt.buf

1 insert into (select empno,ename,deptno from emp

2 where deptno=20

3 with check option)

4* values (333,'wangwang',20)

SQL> /

1 row created.

SQL> ed

Wrote file afiedt.buf

1 insert into (select empno,ename,deptno from emp

2 where deptno=20

3 with check option)

4* values (333,'wangwang',30)

SQL> /

insert into (select empno,ename,deptno from emp

*

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> ed

Wrote file afiedt.buf

1 insert into (select empno,ename,deptno from emp

2 --where deptno=20

3 with check option)

4* values (335,'wang5wang',30)

SQL> /

1 row created.

-- 结论：内联视图与with check option的视图所起的作用是一样的。

-- The WITH CHECK OPTION keyword prohibits you from changing rows that are not in the subquery.