前言:
面试中遇见一道这样的题目:目前有两张表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更新法 | 关联字段非主键,适用于两表关联 | 非主键关联表更新,速度较快 |
快速游标更新法 | 逻辑较复杂的情况 | 复杂逻辑时效率很高 |