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

newkid

最先答对且答案未经编辑的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
       )
/

a10322495

-------------------------------------------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  正确

newkid

答案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。

王小凡123

楼主：选项B的解释中：”但是这不会报错，子查询从plch_emp表的当前行返回了emp_id“为什么没有列会返回plch_emp表的当前行的emp_id，这是数据库自身的机制么？