OCP-1Z0-051 第39题 视图的WITH CHECK OPTION选项
来源:互联网 发布:防火墙软件下载 编辑:程序博客网 时间:2024/05/21 06:43
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.
答案:B D
(答案是BD,但D选项,查联机文档和实验测试,都不正确,详见下面,如果有朋友有不同的结果,可以共同讨论)
执行下面的命令建立一个视图,显示sales部门员工的IDs和last names
A.允许向employees表中插入行。
B.允许删除员工信息。
C.允许更新已存在员工的jobIDs为任意job ID。
D. 如果使用multitable INSERT语句,允许从视图插入销售部员工的IDs, last names, and job IDs。
A选项不正确,因为视图里不包括非空的department_id列,所以不能插入行
B选项正确
C选项不正确,不能任意更新员工job_id,需要使用WHERE job_id LIKE 'SA_%'进行条件限制。
关于D选项,查联机文档和测试,都不正确
关于D选项,查11g联机文档:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF55094
详细摘录如下:
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_clause
s 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.
四、实验
用多表插入方式,也无法入视图中插入数据。
Table dropped.
SQL> drop view v3;
View dropped.
SQL> CREATE TABLE emp3
2 (employee_id NUMBER(4) 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));
Table created.
SQL> CREATE OR REPLACE VIEW v3 AS
2 SELECT employee_id,last_name,job_id
3 FROM emp3
4 WHERE job_id LIKE 'SA_%'
5 WITH CHECK OPTION;
View created.
SQL> insert all
2 into v3(employee_id,last_name,job_id)
3 select employee_id,last_name,job_id from v3;
into v3(employee_id,last_name,job_id)
*
ERROR at line 2:
ORA-01702: a view is not appropriate here
SQL> insert all
2 into v3(employee_id,last_name,job_id)
3 select employee_id,last_name,job_id from emp3;
into v3(employee_id,last_name,job_id)
*
ERROR at line 2:
ORA-01702: a view is not appropriate here
SQL> insert all
2 into emp3(employee_id,last_name,job_id)
3 select employee_id,last_name,job_id from v3;
0 rows created.
- OCP-1Z0-051 第39题 视图的WITH CHECK OPTION选项
- OCP-1Z0-051 第2题 视图的WITH CHECK OPTION选项
- 创建视图的with check option选项。
- -------视图with check option选项-------------------
- OCP-1Z0-051 第40题 视图的注意事项
- OCP-1Z0-051 第41题 视图的注意事项
- OCP-1Z0-051 第42题 视图的注意事项
- 1Z0-051 QUESTION 2 选项WITH CHECK OPTION在CREATE VIEW中的使用
- 视图with check option选项详解
- OCP-1Z0-051 第38题 视图注意事项
- OCP-1Z0-051 第162题 视图和表
- OCP-1Z0-051 第43题 序列的cycle选项
- 视图 WITH CHECK OPTION
- OCP-1Z0-051 补充题库 第14题 关于表约束的视图
- ORACLE 视图的 with check option
- 视图中with check option的作用
- ORACLE 视图的 with check option
- OCP-1Z0-051 第30题 主外键约束,check约束
- 2014编程之美初赛第一场第二题
- PHP中函数小全
- 结构体
- 给王菲写的一首歌《四季之恋》
- Keil工程到IAR工程的移植
- OCP-1Z0-051 第39题 视图的WITH CHECK OPTION选项
- 17.完全二叉树 构建 最大堆 元素的添加和删除(堆排)
- 如何在Word 图表目录中只显示部分图题或表题内容(插入表目录)
- LA 4513 Stammering Aliens(哈希写起来比后缀数组爽多了)
- CGAL4.4_2D限定三角化及网格(2D Conforming Triangulations and Meshes)
- 关于自己
- 第一场 第二题
- Windows7+CentOS双系统同一硬盘
- java模拟form表单提交图片文件Posted by