SQL入門之8 限制插入資料的範圍

insert into 中使用內聯檢視

[@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.