insert实现限制性插入

限制性插入
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

这样就实现限制性插入了