ocp-047-24

24.View the Exhibit and examine the structure of the ORDERS table:

The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the
NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id,order_date,customer_id
FROM ORDERS
WHERE order_total = 1000 WITH CHECK OPTION)
VALUES (13, SYSDATE, 101).
What would be the outcome of the above INSERT statement?

A. It would execute successfully and the new row would be inserted into a
new temporary table created by the subquery.
B. It would execute successfully and the ORDER_TOTAL column would have the
value 1000 inserted automatically in the new row.
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D. It would not execute successfully because all the columns from the ORDERS
table should have been included in the SELECT list and values should have
been provided for all the columns.

Answer: C

实验：

create table ORDERS
(
ORDER_ID NUMBER(12) not null,
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE,
ORDER_MODE VARCHAR2(8),
CUSTOMER_ID NUMBER(6),
ORDER_STATUS NUMBER(2),
ORDER_TOTAL NUMBER(8,2),
SALES_REP_ID NUMBER(6),
PROMOTION_ID NUMBER(6)
)

一、如果不加WITH CHECK OPTION 则在插入时不会检查，插入的值并不受约束条件限制，并可成功插入,插入列为SELECT 后面的三个列。

insert into
(select od.order_id,od.order_date,od.customer_id from orders od where od.order_total=1000 )
values (13,sysdate,101)；

commit;

select * from orders od where od.order_id=13；

返回

ORDER_ID ORDER_DATE ORDER_MODE CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
------------- -------------------------------------------------------------------------------- ---------- ----------- ------------ ----------- ------------ ------------
13 25-7月 -13 12.59.04.000000 下午 101

二、加上with option check ，报错。
insert into
(select od.order_id,od.order_date,od.customer_id from orders od where od.order_total=1000 with check option)
values (13,sysdate,101)

ORA-01402: 视图 WITH CHECK OPTION where 子句违规

三、有order_total=1000的记录，插入的列有不在子查询里作为检查的where 条件的话，不允许插入。

插入一条新记录,order_total=1000：

insert into orders values( 2459,sysdate,'direct',101,0,1000,153,null)

commit;

select * from orders t where t.order_total=1000

ORDER_ID ORDER_DATE ORDER_MODE CUSTOMER_ID ORDER_STATUS ORDER_TOTAL SALES_REP_ID PROMOTION_ID
------------- -------------------------------------------------------------------------------- ---------- ----------- ------------ ----------- ------------ ------------
2459 25-7月 -13 01.21.17.000000 下午 direct 101 0 1000.00 153

insert into
(select od.order_id,od.order_date,od.customer_id from orders od where od.order_total=1000 with check option)
values (13,sysdate,101)
ORA-01402: 视图 WITH CHECK OPTION where 子句违规

四、将限制的字段，加入插入列，入的对应列值不满足subquery 里的where 条件的话，就不允许插入。
insert into
(select od.order_id,od.order_date,od.customer_id,od.order_total from orders od where od.order_total=1000 with check option)
values (13,sysdate,101,999)
insert into
(select od.order_id,od.order_date,od.customer_id,od.order_total from orders od where od.order_total=1000 with check option)
values (13,sysdate,101,999)

ORA-01402: 视图 WITH CHECK OPTION where 子句违规

insert into
2 (select od.order_id,od.order_date,od.customer_id,od.order_total from orders od where od.order_total=1000 with check option)
3 values (14,sysdate,101,1000)
4 ;

1 row inserted