OCP-1Z0-051-V9.02-39题

39. Evaluate the following command: 

CREATE TABLE employees

(employee_id      NUMBER(2) PRIMARY KEY, 

last_name        VARCHAR2(25) NOT NULL, 

department_id    NUMBER(2)NOT NULL, 

job_id           VARCHAR2(8), 

salary        NUMBER(10,2));

You issue the following command to create a view that displays the IDs and last names of the sales staff

in the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS   

SELECT employee_id, last_name,job_id

FROM employees

WHERE job_id LIKE 'SA_%' 

WITH CHECK OPTION

Which two statements are true regarding the above view? (Choose two.)

A. It allows you to insert rows into the  EMPLOYEES table .

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.

C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table. 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements.

Answer: BD

答案解析：

如果硬要选两个，我觉得应该是BC

参考： http://blog.csdn.net/rlhua/article/details/12790467

A错误，因为employees表的department_id有非空约束，如果通过视图插入，则department_id列则为空，会报错。

B正确，可以通过视图来删除employees任意行。

C错误，因为有 WITH CHECK OPTION，是根据 job_id LIKE 'SA_%' 来创建的视图，所以不能更改为不是SA_开头的，但是可以更新为SA_开头的。

D错误，也是因为 department_id有非空约束，如果通过视图插入，则department_id列则为空，会报错。

但是可以通过 multitable INSERT在视图上插入，所有官方文档有错误？

参考： http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF55094

官方文档说明， multitable INSERT  不能用在视图上，故D是错误的。

Restrictions on Multitable Inserts Multitable inserts are subject to the following restrictions:

• You can perform multitable inserts only on tables, not on views or materialized views.

• You cannot perform a multitable insert into a remote table.

• You cannot specify a TABLE collection expression when performing a multitable insert.

• In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.

• Multitable inserts are not parallelized if any target table is index organized or if any target table has a bitmap index defined on it.

• Plan stability is not supported for multitable insert statements.

• You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.

  
  

  实验验证：

  
  

  hr@TEST1107> create table emp

    2  (employee_id number primary key,

    3  last_name varchar2(25) not null,

    4  department_id number(2) not null,

    5  job_id varchar2(8),

    6  salary number(10,2)

    7  );

  
  

  Table created.

  
  

  hr@TEST1107> create or replace view sales_staff_vu as

    2  select employee_id,last_name,job_id

    3  from emp

    4  where job_id like 'SA_%'

    5  with check option

    6  /

  
  

  View created.

  hr@TEST1107> insert into emp select EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID,JOB_ID,SALARY from employees

    2  where job_id like 'SA_%' and rownum<6

    3  /

  
  

  5 rows created.

  
  

  hr@TEST1107> select * from emp;

  
  

  EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID JOB_ID       SALARY

  ----------- ------------------------- ------------- -------- ----------

          145 Russell                              80 SA_MAN        14000

          146 Partners                             80 SA_MAN        13500

          147 Errazuriz                            80 SA_MAN        12000

          148 Cambrault                            80 SA_MAN        11000

          149 Zlotkey                              80 SA_MAN        10500

  
  

  hr@TEST1107> select * from sales_staff_vu;

  
  

  EMPLOYEE_ID LAST_NAME                 JOB_ID

  ----------- ------------------------- --------

          145 Russell                   SA_MAN

          146 Partners                  SA_MAN

          147 Errazuriz                 SA_MAN

          148 Cambrault                 SA_MAN

          149 Zlotkey                   SA_MAN

  
  

  
  

   A答案：A答案错误，DEPARTMENT_ID为非空，不能插入null值。

  hr@TEST1107> insert into sales_staff_vu values (501,'lihua','SA_MAN');

  insert into sales_staff_vu values (501,'lihua','SA_MAN')

  *

  ERROR at line 1:

  ORA-01400: cannot insert NULL into ("HR"."EMP"."DEPARTMENT_ID")

  
  

  B答案：正确，可以删除

  hr@TEST1107> delete from sales_staff_vu where EMPLOYEE_ID=145;

  
  

  1 row deleted.

  
  

  hr@TEST1107> select * from sales_staff_vu;

  
  

  EMPLOYEE_ID LAST_NAME                 JOB_ID

  ----------- ------------------------- --------

          146 Partners                  SA_MAN

          147 Errazuriz                 SA_MAN

          148 Cambrault                 SA_MAN

          149 Zlotkey                   SA_MAN

  
  

  hr@TEST1107> select * from emp;

  
  

  EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID JOB_ID       SALARY

  ----------- ------------------------- ------------- -------- ----------

          146 Partners                             80 SA_MAN        13500

          147 Errazuriz                            80 SA_MAN        12000

          148 Cambrault                            80 SA_MAN        11000

          149 Zlotkey                              80 SA_MAN        10500

  
  

  C答案：可以将job_id更新为SA_开头的，不能更新为其他不是SA_开头的。

  hr@TEST1107> update sales_staff_vu set JOB_ID='SA_REP' where EMPLOYEE_ID=146;

  
  

  1 row updated.

  
  

  hr@TEST1107> update sales_staff_vu set JOB_ID='MK_MAN' where EMPLOYEE_ID=147;

  update sales_staff_vu set JOB_ID='MK_MAN' where EMPLOYEE_ID=147

         *

  ERROR at line 1:

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

  
  

  D答案：multitable INSERT不能插入非空

  hr@TEST1107> insert into sales_staff_vu 

    2  select EMPLOYEE_ID,LAST_NAME,JOB_ID from employees

    3  where job_id like 'SA_%'

    4  /

  insert into sales_staff_vu

  *

  ERROR at line 1:

  ORA-01400: cannot insert NULL into ("HR"."EMP"."DEPARTMENT_ID")

  
  

  实验证明，multitable INSERT可以插入视图？

  hr@TEST1107> create or replace view sales_staff_vu_new as

    2  select employee_id,last_name,job_id,department_id from emp

    3  where job_id like 'SA_%'

    4  with check option

    5  /

  
  

  View created.

  
  

  hr@TEST1107> insert into sales_staff_vu_new

    2  select EMPLOYEE_ID,LAST_NAME,JOB_ID,DEPARTMENT_ID from employees where job_id like 'SA_%'

    3  and EMPLOYEE_ID in (150,151);

  
  

  2 rows created.

  
  

  hr@TEST1107> select * from sales_staff_vu_new;

  
  

  EMPLOYEE_ID LAST_NAME                 JOB_ID   DEPARTMENT_ID

  ----------- ------------------------- -------- -------------

          146 Partners                  SA_REP              80

          147 Errazuriz                 SA_MAN              80

          148 Cambrault                 SA_MAN              80

          149 Zlotkey                   SA_MAN              80

          150 Tucker                    SA_REP              80

          151 Bernstein                 SA_REP              80

  
  

  6 rows selected.