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.