OCP-1Z0-051-V9 02-39题中关于数据库连接池配置的最佳实践是什么？

本文共计1526个文字，预计阅读时间需要7分钟。

39. 评估以下SQL命令：创建一个名为`employees`的表，包含以下字段：`employee_id`（数字，长度为2，为主键），`last_name`（可变字符，长度为25，不能为空），`department_id`（数字，长度为2，不能为空），`job_id`（可变字符，长度为8），`salary`（数字，长度为10，小数点后2位）。

39. Evaluate the following command: 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 inmultitable INSERT statements. Answer: BD 答案解析： 如果硬要选两个，我觉得应该是BC 参考： www.voidcn.com/article/p-wumknikm-bbu.html A错误，因为employees表的department_id有非空约束，如果通过视图插入，则department_id列则为空，会报错。 B正确，可以通过视图来删除employees任意行。 C错误，因为有 WITH CHECK OPTION，是根据 job_id LIKE ‘SA_%‘ 来创建的视图，所以不能更改为不是SA_开头的，但是可以更新为SA_开头的。 D错误，也是因为 department_id有非空约束，如果通过视图插入，则department_id列则为空，会报错。 但是可以通过 multitable INSERT在视图上插入，所有官方文档有错误？
参考： docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF55094 官方文档说明， multitable INSERT 不能用在视图上，故D是错误的。

Restrictions on Multitable InsertsMultitable 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 aTABLEcollection expression when performing a multitable insert.

• In a multitable insert, all of theinsert_into_clauses 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 toNEXTVALgenerates the next number, and all subsequent references in the statement return the same number.

  实验验证：

  [emailprotected]> create table emp

  2 (employee_id number 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)

  7 );

  Table created.

  

  [emailprotected]> create or replace view sales_staff_vu as

  2 select employee_id,last_name,job_id

  3 from emp

  4 where job_id like ‘SA_%‘

  5 with check option

  6 /

  View created.

  [emailprotected]> insert into emp select EMPLOYEE_ID,LAST_NAME,DEPARTMENT_ID,JOB_ID,SALARY from employees

  2 where job_id like ‘SA_%‘ and rownum<6

  3 /

  5 rows created.

  [emailprotected]> select * from emp;

  EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY

  ----------- ------------------------- ------------- -------- ----------

  145 Russell 80 SA_MAN 14000

  146 Partners 80 SA_MAN 13500

  147 Errazuriz 80 SA_MAN 12000

  148 Cambrault 80 SA_MAN 11000

  149 Zlotkey 80 SA_MAN 10500

  [emailprotected]> select * from sales_staff_vu;

  EMPLOYEE_ID LAST_NAME JOB_ID

  ----------- ------------------------- --------

  145 Russell SA_MAN

  146 Partners SA_MAN

  147 Errazuriz SA_MAN

  148 Cambrault SA_MAN

  149 Zlotkey SA_MAN

  A答案：A答案错误，DEPARTMENT_ID为非空，不能插入null值。

  [emailprotected]> insert into sales_staff_vu values (501,‘lihua‘,‘SA_MAN‘);

  insert into sales_staff_vu values (501,‘lihua‘,‘SA_MAN‘)

  *

  ERROR at line 1:

  ORA-01400: cannot insert NULL into ("HR"."EMP"."DEPARTMENT_ID")

  B答案：正确，可以删除

  [emailprotected]> delete from sales_staff_vu where EMPLOYEE_ID=145;

  1 row deleted.

  [emailprotected]> select * from sales_staff_vu;

  EMPLOYEE_ID LAST_NAME JOB_ID

  ----------- ------------------------- --------

  146 Partners SA_MAN

  147 Errazuriz SA_MAN

  148 Cambrault SA_MAN

  149 Zlotkey SA_MAN

  [emailprotected]> select * from emp;

  EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID SALARY

  ----------- ------------------------- ------------- -------- ----------

  146 Partners 80 SA_MAN 13500

  147 Errazuriz 80 SA_MAN 12000

  148 Cambrault 80 SA_MAN 11000

  149 Zlotkey 80 SA_MAN 10500

  C答案：可以将job_id更新为SA_开头的，不能更新为其他不是SA_开头的。

  [emailprotected]> update sales_staff_vu set JOB_ID=‘SA_REP‘ where EMPLOYEE_ID=146;

  1 row updated.

  [emailprotected]> update sales_staff_vu set JOB_ID=‘MK_MAN‘ where EMPLOYEE_ID=147;

  update sales_staff_vu set JOB_ID=‘MK_MAN‘ where EMPLOYEE_ID=147

  *

  ERROR at line 1:

  ORA-01402: view WITH CHECK OPTION where-clause violation

  D答案：multitable INSERT不能插入非空

  [emailprotected]> insert into sales_staff_vu

  2 select EMPLOYEE_ID,LAST_NAME,JOB_ID from employees

  3 where job_id like ‘SA_%‘

  4 /

  insert into sales_staff_vu

  *

  ERROR at line 1:

  ORA-01400: cannot insert NULL into ("HR"."EMP"."DEPARTMENT_ID")

  实验证明，multitable INSERT可以插入视图？

  [emailprotected]> create or replace view sales_staff_vu_new as

  2 select employee_id,last_name,job_id,department_idfrom emp

  3 where job_id like ‘SA_%‘

  4 with check option

  5 /

  View created.

  [emailprotected]> insert into sales_staff_vu_new

  2 select EMPLOYEE_ID,LAST_NAME,JOB_ID,DEPARTMENT_ID from employees where job_id like ‘SA_%‘

  3 and EMPLOYEE_ID in (150,151);

  2 rows created.

  [emailprotected]> select * from sales_staff_vu_new;

  EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

  ----------- ------------------------- -------- -------------

  146 Partners SA_REP 80

  147 Errazuriz SA_MAN 80

  148 Cambrault SA_MAN 80

  149 Zlotkey SA_MAN 80

  150 Tucker SA_REP 80

  151 Bernstein SA_REP 80

  6 rows selected.

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！希望你也加入到我们人工智能的队伍中来！www.captainbed.net