【OCP】Oracle 11g OCP 1Z0-051 002

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
考点:通过视图对基表进行insert操作时，基表中带有not null约束的列须出现在视图中。
关键字：SELECT * FROM


【解析实验】

题意要求可以对视图进行所有DML操作（insert 、update、delete），
如果通过视图对基表进行insert操作，创建视图的语句必须包含基表中所有带有not null约束限制的列
本题基表上各列均有not null约束限制，所以创建视图时必须包含基表所有列。CD选项错误.

语法上看ABC选项视图均可以正常创建，但C选项视图v2不可以进行insert操作。


对C选项中视图v2执行insert操作时，相当于对基表SALES部分列执行insert操作，其他列由于有not null约束，会报错。
SH@PROD>insert into v2 values (20,2411,to_date('26-JUN-98','DD-MON-RR'));



D选项带有group by子句，使用了sum函数，是复杂视图，不能对复杂视图进行DML操作更新基表。D错误
D选项本身语法也有错误， SUM(quantity_sold) 后需要使用别名才能正常创建视图

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29475508/viewspace-2065229/，如需转载，请注明出处，否则将追究法律责任。