insert实现限制性插入

忘是亡心i 2022-06-10 22:38 151阅读 0赞

限制性插入  
insert into后面跟一个子查询  
INSERT INTO  
(SELECT employee\_id, last\_name,  
email, hire\_date, job\_id, salary,  
department\_id  
FROM   employees  
WHERE  department\_id = 50 )  
VALUES (99999, 'Taylor', 'DTAYLOR',  
TO\_DATE('27-05-2012', 'DD-MM-YYYY'),  
'ST\_CLERK', 5000, 50);  
上面表示向50部门里插入一个员工但是把上面values里的50改为60就是向60部门里插入了  
SQL> INSERT INTO  
  2  (SELECT employee\_id, last\_name,  
  3  email, hire\_date, job\_id, salary,  
  4  department\_id  
  5  FROM   employees  
  6  WHERE  department\_id = 50 )  
  7  VALUES (99999, 'Taylor', 'DTAYLOR',  
  8  TO\_DATE('27-05-2012', 'DD-MM-YYYY'),  
  9  'ST\_CLERK', 5000, 50);

已创建 1 行。

SQL> select employee\_id,last\_name,department\_id from employees;

EMPLOYEE\_ID LAST\_NAME                                          DEPARTMENT\_ID  
\----------- -------------------------------------------------- -------------  
      99999 Taylor                                                        60  
        198 OConnell                                                      50  
        199 Grant                                                         50  
        200 Whalen                                                        10  
        201 Hartstein                                                     20  
现在要实现只向50部门里插入员工，向其他部门插入会报错也就是限制只能向50部门里插入，只需要在where之后加上WITH CHECK OPTION就可以了  
INSERT INTO  
(SELECT employee\_id, last\_name,  
email, hire\_date, job\_id, salary,  
department\_id  
FROM   employees  
WHERE  department\_id = 50 WITH CHECK OPTION)  
VALUES (99999, 'Taylor', 'DTAYLOR',  
TO\_DATE('27-05-2012', 'DD-MM-YYYY'),  
'ST\_CLERK', 5000, 60);  
此时如果插入部门不是50就会报错  
SQL> INSERT INTO  
  2  (SELECT employee\_id, last\_name,  
  3  email, hire\_date, job\_id, salary,  
  4  department\_id  
  5  FROM   employees  
  6  WHERE  department\_id = 50 WITH CHECK OPTION)  
  7  VALUES (99999, 'Taylor', 'DTAYLOR',  
  8  TO\_DATE('27-05-2012', 'DD-MM-YYYY'),  
  9  'ST\_CLERK', 5000, 60);  
FROM   employees  
       \*  
第 5 行出现错误:  
ORA-01402: 视图 WITH CHECK OPTION where 子句违规

插入50部门就不会报错  
SQL> ed  
已写入 file afiedt.buf

1  INSERT INTO  
  2  (SELECT employee\_id, last\_name,  
  3  email, hire\_date, job\_id, salary,  
  4  department\_id  
  5  FROM   employees  
  6  WHERE  department\_id = 50 WITH CHECK OPTION)  
  7  VALUES (99999, 'Taylor', 'DTAYLOR',  
  8  TO\_DATE('27-05-2012', 'DD-MM-YYYY'),  
  9\* 'ST\_CLERK', 5000, 50)  
SQL> /

已创建 1 行。

SQL> select employee\_id,last\_name,department\_id from employees;

EMPLOYEE\_ID LAST\_NAME                                          DEPARTMENT\_ID  
\----------- -------------------------------------------------- -------------  
      99999 Taylor                                                        50  
        198 OConnell                                                      50  
        199 Grant                                                         50

这样就实现限制性插入了