最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正)，其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
每两周的优胜者可获得itpub奖励的技术图书一本。
以往旧题索引：
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处：
http://www.plsqlchallenge.com/
作者：
ChrisSaxon
运行环境：SQLPLUS, SERVEROUTPUT已打开
注：本题给出答案时候要求给予简要说明才能得到奖品
你正在创建一个应用，来给机器人编程。它会在如下的表中存储编写好的详细路径信息：
create table qz_directions (
direction varchar2(1) not null primary key
);
create table qz_robot_directions (
route_idint not null,
step_number int not null,
direction varchar2(1) not null,
distancenumber not null,
primary key ( route_id, step_number ) ,
constraint qz_direction_c foreign key ( direction )
references qz_directions ( direction )
);
insert into qz_directions values ('N');
insert into qz_directions values ('E');
insert into qz_directions values ('S');
insert into qz_directions values ('W');
commit;
四个方向 N, E, S 和 W 分别代表着北(North), 南(South), 东(East)和西(West)。你想要进行扩展，使得可以保存下列的方向：
NE (东北North East)
SE (东南South East)
SW (西南South West)
NW (西北North West)
哪些选项修改了上述的表，使得你可以执行如下的数据插入：
前两个成功地各插入了一行；
后两个报错?
/* 这两个 insert 成功执行 */
insert into qz_robot_directions (
route_id, step_number, direction, distance
) values (
1, 1, 'NW', 10
);
insert into qz_robot_directions (
route_id, step_number, direction, distance
) values (
1, 2, 'SE', 15
);
/* 这两个 insert 报错 */
insert into qz_robot_directions (
route_id, step_number, direction, distance
) values (
1, 3, 'NZ', 25
);
insert into qz_robot_directions (
route_id, step_number, direction, distance
) values (
1, 4, 'NN', 20
);
(A)
alter table qz_robot_directions drop constraint qz_direction_c ;
alter table qz_robot_directions modify ( direction varchar2(2) );
alter table qz_robot_directions add constraint qz_direction_c check (
direction in ( 'N', 'NE', 'E', 'SE', 'S', 'SW', 'W', 'NW' )
) ;
(B)
alter table qz_directions modify ( direction varchar2(2) );
alter table qz_robot_directions modify ( direction varchar2(2) );
insert into qz_directions values ('NW');
insert into qz_directions values ('NE');
insert into qz_directions values ('SE');
insert into qz_directions values ('SW');
commit;
(C)
alter table qz_robot_directions drop constraint qz_direction_c ;
alter table qz_robot_directions modify ( direction varchar2(2) );
alter table qz_robot_directions add (
d1 as ( substr ( direction, 1, 1 ) ),
d2 as ( substr ( direction, 2, 1 ) )
);
alter table qz_robot_directions add constraint qz_direction_c
foreign key ( d1 )
references qz_directions ( direction ) ;
alter table qz_robot_directions add constraint qz_direction_c2
foreign key ( d2 )
references qz_directions ( direction ) ;
(D)
alter table qz_robot_directions drop constraint qz_direction_c ;
alter table qz_robot_directions modify ( direction varchar2(2) ) ;
alter table qz_robot_directions add constraint qz_direction_c
foreign key ( substr ( direction, 1, 1 ) )
references qz_directions ( direction ) ;
alter table qz_robot_directions add constraint qz_direction_c2
foreign key ( substr ( direction, 2, 1 ) )
references qz_directions ( direction ) ;
|
