ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 2721|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2016-12-14 关联删除

[复制链接]
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2016-12-20 05:59 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我有两张表,部门和员工:

create table plch_dept (
   dept_id  integer      primary key
, name     varchar2(10) unique
)
/

create table plch_emp (
   emp_id   integer      primary key
, name     varchar2(10) not null
, dept_id  integer      not null references plch_dept
)
/

insert into plch_dept values (100, 'Marketing')
/
insert into plch_dept values (200, 'Service')
/

insert into plch_emp values (10, 'Jones', 100)
/
insert into plch_emp values (15, 'Smith', 100)
/
insert into plch_emp values (20, 'Baker', 200)
/
insert into plch_emp values (25, 'Saxon', 200)
/

commit
/

公司要将市场部(Marketing)外包,所以我们需要删除所有在部门'Marketing'工作的员工。

哪些选项包含一个删除语句可以执行不出错,成功将Jones 和 Smith删除,使得选项执行之后,下列的测试查询会产生如下的输出:

select emp_id, name, dept_id
  from plch_emp
order by emp_id
/

    EMP_ID NAME          DEPT_ID
---------- ---------- ----------
        20 Baker             200
        25 Saxon             200

(A)
delete plch_emp
where dept_id = (
   select dept_id
     from plch_dept
    where name = 'Marketing'
       )
/

(B)
delete plch_emp
where emp_id = (
   select emp_id
     from plch_dept
    where name = 'Marketing'
       )
/

(C)
delete plch_emp e
where e.dept_id = (
   select d.dept_id
     from plch_dept d
    where d.name = 'Marketing'
       )
/

(D)
delete plch_emp e
where e.emp_id = (
   select d.emp_id
     from plch_dept d
    where d.name = 'Marketing'
       )
/

(E)
delete plch_emp
where dept_id IN (
   select dept_id
     from plch_dept
    where name = 'Marketing'
       )
/

(F)
delete plch_emp
where emp_id IN (
   select emp_id
     from plch_dept
    where name = 'Marketing'
       )
/

(G)
delete plch_emp
where EXISTS (
   select null
     from plch_dept
    where name = 'Marketing'
      and dept_id = dept_id
       )
/

(H)
delete plch_emp e
where EXISTS (
   select null
     from plch_dept d
    where d.name = 'Marketing'
      and d.dept_id = e.dept_id
       )
/

认证徽章
论坛徽章:
14
秀才
日期:2016-11-25 16:52:36秀才
日期:2017-01-20 11:00:36奥运会纪念徽章:跳水
日期:2016-09-13 10:47:52ITPUB15周年纪念
日期:2016-10-13 13:15:34布鲁克
日期:2016-10-12 08:34:20奥运会纪念徽章:皮划艇激流回旋
日期:2016-09-21 17:33:26优秀写手
日期:2014-08-14 06:00:13咸鸭蛋
日期:2012-12-05 15:47:46ITPUB 11周年纪念徽章
日期:2012-10-09 18:08:15奥运会纪念徽章:跳水
日期:2012-09-05 16:10:53
发表于 2016-12-20 10:16 | 显示全部楼层

-------------------------------------------ACEH  正确

(A) ----正确 直接删除 Marketing 编号下的所有员工
(B) ----错误 表plch_dept 没有emp_id 字段,  造成了表plch_emp 数据全部删除。
(C) ----正确 直接删除 Marketing 编号下的所有员工   这个与A的区别是给表添加了别名。
(D) ----错误  表plch_dept 没有emp_id 字段,  造成了表plch_emp 数据全部删除。
(E) ----正确 直接删除 Marketing 编号下的所有员工 和A的区别是 IN 和= 的区别,由于dept_id 为plch_dept 表的主建,所以正确。
(F)----错误 表plch_dept 无 emp_id 字段  
(G)---错误  内别关联条件 and dept_id = dept_id   分别不出来,那个是部门和员工, 造成,数据全部删除。
(H)----正确,  弥补了 G  的错误。   

-------------------------------------------ACEH  正确

使用道具 举报

回复
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2016-12-21 05:21 | 显示全部楼层
答案ACEH, 2楼得奖。

A:
因为plch_dept上的子查询返回单独一行(此处我们可以确保这一点,因为name上有唯一索引),等值谓词在这子查询上没有问题。如果子查询产生多于一行,我们就会得到ORA-01427错误。

B: 此处我们犯了一个错,从plch_dept选择了emp_id列,而这个列在表上不存在。但是这不会报错,子查询从plch_emp表的当前行返回了emp_id。因此等值谓词永远为真,它删除了4行,导致测试查询给出了这个错误结果:

no rows selected

C:
此处我们用了表的别名,很清楚地指出每个列属于哪个表。这会得到和A选项相同的结果,而这结果是正确的。这个选项只是更加可读了一点。

D: 这选项包含了和选项B相同的错误,但是既然我们用了表别名,它不能被解析,而是会报错:
ORA-00904: "D"."EMP_ID": invalid identifier.
这实际上是好事,因为我们就可以在编译的时候捕获这个错误,而不是在执行时才试图去找出一个删除了所有数据的神秘错误。

E: 在这种情况下,既然plch_dept表中的name是唯一的,这会得到和选项A一样的结果。然而,和选项A不同的是,如果子查询产生了多行结果,这个选项仍然可以工作。

F: IN子句疑似有着和选项B相同的问题,这也会删除所有的4行数据。如果能像选项D那样使用表别名,在编译时而非执行时就捕获这个错误,那将会是个好主意。

G: EXISTS是另一种方法,但是我们再次碰到了缺失表别名的麻烦。子查询中的两个dept_id列都同样会被解释为plch_dept表的dept_id,所以子查询对于每个员工都永远会返回一行,使得EXISTS子句永远为真,所以这也会删除所有的4行数据。

H: 前一选项的问题再次通过使用表别名得到了解决,它明确告诉解释器,我们在两种情形所需的是哪个dept_id。

使用道具 举报

回复
论坛徽章:
0
发表于 2016-12-23 14:50 | 显示全部楼层
楼主:选项B的解释中:”但是这不会报错,子查询从plch_emp表的当前行返回了emp_id“为什么没有列会返回plch_emp表的当前行的emp_id,这是数据库自身的机制么?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表