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,serif; font-size: 13.63px'> department_id??? NUMBER(2)NOT NULL,serif; font-size: 13.63px'> job_id?????????? VARCHAR2(8),serif; font-size: 13.63px'> 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://www.voidcn.com/article/p-wumknikm-bbu.html

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.

  ?

  实验验证：

  ?

  [email?protected]> create table emp

  ? 2 ?(employee_id number primary key,

  ? 3 ?last_name varchar2(25) not null,sans-serif; font-size: 12px">? 4 ?department_id number(2) not null,sans-serif; font-size: 12px">? 5 ?job_id varchar2(8),sans-serif; font-size: 12px">? 6 ?salary number(10,2)

  ? 7 ?);

  ?

  Table created.

  [email?protected]> create or replace view sales_staff_vu as

  ? 2 ?select employee_id,job_id

  ? 3 ?from emp

  ? 4 ?where job_id like ‘SA_%‘

  ? 5 ?with check option

  ? 6 ?/

  View created.

  ?

  ?

  [email?protected]> 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.

  [email?protected]> 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

  [email?protected]> 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值。

  ?

  ?

  [email?protected]> insert into sales_staff_vu values (501,‘lihua‘,‘SA_MAN‘);

  insert into sales_staff_vu values (501,‘SA_MAN‘)

  *

  ERROR at line 1:

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

  ?

  B答案：正确，可以删除

  ?

  [email?protected]> delete from sales_staff_vu where EMPLOYEE_ID=145;

  1 row deleted.

  [email?protected]> 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

  [email?protected]> 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_开头的。

  ?

  [email?protected]> update sales_staff_vu set JOB_ID=‘SA_REP‘ where EMPLOYEE_ID=146;

  1 row updated.

  [email?protected]> 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

  ? ? ? ?*

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

  ?

  ?

  ?

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

  ?

  ?

  [email?protected]> insert into sales_staff_vu?

  ? 2 ?select EMPLOYEE_ID,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可以插入视图？

  ?

  [email?protected]> create or replace view sales_staff_vu_new as

  ? 2 ?select employee_id,job_id,department_id?from emp

  ? 3 ?where job_id like ‘SA_%‘

  ? 4 ?with check option

  ? 5 ?/

  View created.

  ?

  ?

  ?

  [email?protected]> insert into sales_staff_vu_new

  ? 2 ?select EMPLOYEE_ID,DEPARTMENT_ID from employees where job_id like ‘SA_%‘

  ? 3 ?and EMPLOYEE_ID in (150,151);

  2 rows created.

  ?

  ?

  [email?protected]> 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.