1Z0-051 QUESTION 2 选项WITH CHECK OPTION在CREATE VIEW中的使用

QUESTION 2
View the Exhibit to examine the description for the SALES table.

Which views can have all DML operations performed on it? (Choose all that apply.)

A. CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;

答案：AB

解析：

SQL> --create table
SQL> create table sales (
  2  prod_id number not null,
  3  cust_id number not null,
  4  time_id date not null,
  5  channel_id number not null,
  6  promo_id number not null,
  7  quantity_sold number(10,2) not null);


表已创建。


SQL> --选项A，能够创建而且还能使用所有的DML语句
SQL> create view v3
  2  as select * from sales
  3  where cust_id=2034
  4  with check option;


视图已创建。


SQL> insert into v3 values(1,2034,sysdate,2,3,4);


已创建 1 行。

SQL> update v3 set prod_id=2 where cust_id=2034;


已更新 1 行。

SQL> delete from v3 where cust_id=2034;


已删除 1 行。

SQL> --选项B，能够创建而且还能使用所有的DML语句
SQL> CREATE VIEW v1
  2  AS SELECT * FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  WITH CHECK OPTION;


视图已创建。

SQL> insert into v1 values(1,2,sysdate-3*365,3,4,5);


已创建 1 行。


SQL> update v1 set prod_id=2 where prod_id=1;


已更新 1 行。


SQL> delete from v1 where  prod_id=2;


已删除 1 行。

SQL> --选项C，可以创建视图但DML语句会报错，无法正常insert；
SQL> CREATE VIEW v2
  2  AS SELECT prod_id, cust_id, time_id FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  WITH CHECK OPTION;


视图已创建。


SQL> --insert 视图中的3个字段，报错；
SQL> insert into v2 values(1,2,sysdate-3*365);
insert into v2 values(1,2,sysdate-3*365)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("YBS"."SALES"."CHANNEL_ID")




SQL> --insert 表中的所有字段，也报错；
SQL> insert into v2 values(1,2,sysdate-3*365,3,4,5);
insert into v2 values(1,2,sysdate-3*365,3,4,5)
            *
第 1 行出现错误:
ORA-00913: 值过多

SQL> --选项D，无法正常创建，报错；
SQL> CREATE VIEW v4
  2  AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  GROUP BY prod_id, cust_id
  5  WITH CHECK OPTION;
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
                            *
第 2 行出现错误:
ORA-00998: 必须使用列别名命名此表达式




SQL> --给一个别名后创建成功；
SQL> CREATE VIEW v4
  2  AS SELECT prod_id, cust_id, SUM(quantity_sold) as sumquty FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  GROUP BY prod_id, cust_id
  5  WITH CHECK OPTION;


视图已创建。