OCP-1Z0-051 第2题 视图的WITH CHECK OPTION选项

一、原题

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

二、题目翻译

下图是SALES表的描述。
可以在哪一个视图上面执行所有的DML操作？（选择所有正确的项）

三、题目解析

WITH CHECK OPTION选项：Oracle数据库禁止更改不包含在子查询条件里的行。
C不正确的是因为，视图只建了三列，而其它列不能为空，
D不正确的是因为聚合函数SUM(quantity_sold)需要一个别名，并且有聚合函数就是复杂视图，复杂视图是不能进行DML操作的。

四、实验
      1. A选项：可以进行DML操作

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

Table created.

SQL> CREATE VIEW v3
  2  AS SELECT * FROM SALES
  3  WHERE cust_id = 2034
  4  WITH CHECK OPTION;

View created.

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

1 row created.

SQL> update v3 set promo_id=8 where cust_id=2034;

1 row updated.

SQL> delete from v3 where cust_id=2034;

1 row deleted.

         2.B选项:可以进行DML操作

SQL> CREATE VIEW v1
  2  AS SELECT * FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  WITH CHECK OPTION;

View created.

SQL> insert into v1 values(1,2,to_date('2011-3-4','yyyy-mm-dd'),3,4,5);

1 row created.

SQL> update v1 set channel_id=6 where time_id=to_date('2011-3-4','yyyy-mm-dd');

1 row updated.

SQL> delete from v1  where time_id=to_date('2011-3-4','yyyy-mm-dd');

1 row deleted.

        3.C选项，不能进行DML操作

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;

View created.

--插入视图中的三列，其它的列就为null值，不允许
SQL> insert into v2 values(1,2,to_date('2011-3-4','yyyy-mm-dd'));
insert into v2 values(1,2,to_date('2011-3-4','yyyy-mm-dd'))
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."SALES"."CHANNEL_ID")

--插入所有的列，又违返了with check option约束项，报错
SQL> insert into v2 values(1,2,to_date('2011-3-4','yyyy-mm-dd'),3,4,5);
insert into v2 values(1,2,to_date('2011-3-4','yyyy-mm-dd'),3,4,5)
            *
ERROR at line 1:
ORA-00913: too many values

         4.D选项，聚合函数列需要取一个别名，并且复杂视图，不能进行DML操作

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
                            *
ERROR at line 2:
ORA-00998: must name this expression with a column alias

--将别名补上后，视图可以创建成功
SQL> CREATE VIEW v4
  2  AS SELECT prod_id, cust_id, SUM(quantity_sold) squ FROM SALES
  3  WHERE time_id <= SYSDATE - 2*365
  4  GROUP BY prod_id, cust_id
  5  WITH CHECK OPTION;

View created.

SQL> insert into v4 values(1,2,10);
insert into v4 values(1,2,10)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here