Oracle数据库面试题（一）update更改多行数据

前言：

        面试中遇见一道这样的题目：目前有两张表A表和B表，A表有字段ID,NAME,B表同样有字段ID,NAME（）字段ID和A表的ID相同，但是字段NAME和A表中的NAME不相同），使用B表中的NAME更新A表的NAME。

我立刻想到这么简单呀，update呀。但是又一想不可能呀。下面详细介绍四种方法更改多行数据。

一、传统的方法--update：（可能是最慢的方法）

--在SQL Server中可以这么写（这是我的回答，结果面试官蒙了。长时间使用SQL server的缘故吧）：
update a set a.NAME=b.NAME from A a,B b where a.ID=b.ID --这个效率会很快的。

在Oracle里面却只能写成这样：

update A a set a.NAME=(select b.NAME from B b where a.ID=b.ID)
----或者：使用exists限制一下。
update A a set a.NAME=(select b.NAME from B b where a.ID=b.ID) 
where exists(select 1 from B b where  a.ID=b.ID)

使用Oracle中员工表emp做例子：

可以直接在Oracle数据库中执行，test_emp是emp的相同表结构，复制过来的。
update TEST_EMP a
   set a.sal =
       (select b.sal from emp b where a.empno = b.empno)
 where exists (select 1 from emp b where a.empno = b.empno);

这样的话，假如A,B两张里面各有10000条数据，那么它要全表扫描10000*10000次，就会出现效能问题。那么在oracle里面有什么别的办法来改善吗？

二、inline view内嵌视图：（关联主键字段，速度较快）

方案：

        更新一个临时建立的视图。要求B表的主键字段必须在where条件中，并且是以=号来关联被更新表，否则可能报错：ORA-01779:无法修改与非键值保存表对应的列。当B表主键字段为多列组合时，也有可能出现这一报错。

update 
(
    select a.sal as asal, b.sal as bsal 
    from emp a, test_emp b 
    where a.empno = b.empno
) 
set asal = bsal;

内嵌视图和子查询的区别：（一直认为是一样，看来还是有区别。）
        子查询是完整的查询语句。子查询首先生成结果集，并将结果集应用于条件语句。子查询与内嵌视图不同。内嵌视图也可以看作临时查询结果，但是内嵌视图出现在from子句中，并与其他数据源（数据表、视图等）形成笛卡尔积运算。而子查询单独运算，不会与其他数据源进行笛卡尔积运算。

三、merge into更新法：（关联字段非主键时，速度较快）

方案：

        在alias2中select出来的数据，每一条都跟alias1进行ON (join condition)比较，若匹配，就进行更新操作，不匹配，执行插入操作。
merge不会返回影响行数，且最多只能两表关联，适用于连接条件不是主键的字段。

语法：（详细的merge into的讲解，我另外写了一篇文章介绍：先留白）

merge into table_name  alias1   --需要操作的表 可以用别名
using (table|view|sub_query) alias2      --数据来源 可以是表、视图、子查询
on (join condition)   --关联条件
when matched then              --当关联条件成立时 更新，删除，插入的where部分为可选 
  --更新
  update  table_name set  col1=colvalue  where……
  --删除  
  delete from table_name  where  col2=colvalue  where……
--可以只更新不删除 也可以只删除不更新。
--如果更新和删除同时存在，删除的条件一定要在更新的条件内，否则数据不能删除。
when not matched then      --当关联条件不成立时   插入
  insert (col3) values (col3values)  where……; 

--举例说明：
merge into test_emp  a   --需要操作的表 可以用别名
using emp b        --源表
on (a.EMPNO = b.EMPNO)   --目标表和源表的关联关系
when matched then              --当关联条件成立时 更新，删除，插入的where部分为可选 
  update set  a.sal= b.sal where a.empno = '7566' 
when not matched then    
  insert (a.empno, a.ename, a.job, a.mgr, a.hiredate, a.sal, a.comm, a.deptno) 
  values (b.empno, b.ename, b.job, b.mgr, b.hiredate, b.sal, b.comm, b.deptno);

四、快速游标更新法：（复杂逻辑时，效率很高）

方案：

        配合oracle独有的内置ROWID物理字段，使用快速游标，不需要定义，直接把游标写到for循环中，快速定位并执行更新。它可以支持复杂逻辑的查询语句，更新准确，无论数据多大更新效率依然很高。但执行后不返回影响行数。

语法：

begin
for cr in (查询语句) loop  --循环
update table_name set ...   --更新语句（根据查询出来的结果集合）
end loop;  --结束循环
end;

举例说明：

begin
for emp_cursor in (select a.rowid, b.sal sal from test_emp a, emp b where a.empno = b.empno)
  loop
    update test_emp set sal = emp_cursor.sal
    where rowid = emp_cursor.rowid;
    end loop;
end;

五、四种方法的比较：

方法描述	适用范围	运行效率
传统方案	一般情况适用	单表更新效率高且稳定，多表时效率较慢
inline view更新法	关联字段为主键	速度较快
merge更新法	关联字段非主键，适用于两表关联	非主键关联表更新，速度较快
快速游标更新法	逻辑较复杂的情况	复杂逻辑时效率很高