同时update多张表的语句 梦中的面试

昨天晚上在梦中有这样的场景：自行车爆胎了，修车师傅一边忙活，忽然回头对我说“你是做数据库的吧，考考你。用一条update语句怎么同时修改两张表？” wait4friend石化了几秒钟，然后下意识的给出一个答案。正确与否还不知道，然后就惊醒了。那么当时 wait4friend 给出的答案是啥呢？当时想到的是，利用key-preserved table这个特性来修改视图，达到同时更新多张表的目的。

啥叫key-preserved table？  一个包含多表的视图，是否可以更新，哪些字段可以更新，涉及到一个叫 key-preserved table的概念。简单的说，如果一个表的所有key同时也是视图的key，那么这个表就是kep-preserved table。当视图中仅有一个kpt的时候，可以对kpt的数据进行更新操作。关于key-preseved table和upatable columns的具体信息，请参考手册。

简单点说，就是判断一个视图中的字段是否可以直接修改的条件。

咱们来做个测试，事实说话。环境Oracle11g R2

----------------------------------------------------------------------------

----- key-preserved table

create table ta (x int, y int, z int, constraint pk_ta primary key (x));

create table tb (x int, m int, n int, constraint pk_tb primary key (x));

alter table tb add constraint fk_tb foreign key (x)references ta (x);

insert into ta(x,y,z) values(1,1,1);

insert into ta(x,y,z) values(2,2,2);

insert into tb(x,m,n) values(1,1,1);

commit;

-- 这里建立的视图，两个基表因为主外键的约束，实际上是1:1的关系。满足key-preserved条件

create or replace view tab as select a.x, a.y, a.z, b.m, b.n from ta a, tb b where a.x=b.x;

通过user_updatable_columns字典视图，我们可以查看满足条件的字段。这里看到我们视图TAB的各个字段都满足可更新条件。

SQL> col table_name for a10;

SQL> col column_name for a10;

SQL> select u.table_name,u.column_name,u.updatable,u.insertable,u.deletable from user_updatable_columns u where u.table_name = 'TAB';

TABLE_NAME COLUMN_NAM UPDATABLE INSERTABLE DELETABLE

---------- ---------- --------- ---------- ---------

TAB        X          YES       YES        YES

TAB        Y          YES       YES        YES

TAB        Z          YES       YES        YES

TAB        M          YES       YES        YES

TAB        N          YES       YES        YES

我们来看看实际的更新效果。任何一个基表可以被单独更新，但是同时更新多个基表是不允许的。

SQL> -- 更新其中一个基表

SQL> update tab t set t.y=5, t.z=5;

1 row updated

SQL> 

SQL> -- 更新其中另一个基表

SQL> update tab t set t.m=6, t.n=6;

1 row updated

SQL> 

SQL> -- 同时更新两个基表

SQL> update tab t set t.y=5, t.z=5, t.m=6, t.n=6;

update tab t set t.y=5, t.z=5, t.m=6, t.n=6

ORA-01776: 无法通过联接视图修改多个基表r

到了这里，wait4friend 的梦中面试已经失败了 ：-D

题外话：以前在一家小外企面试的时候，还真有个老外问过我这问题。当时我说，我没有正常的办法实现这个需求，不过可以通过trigger等小花招来实现。然后我就问他有答案没有，结果他说他也是想用trigger，：-） 关于这个，可以看看instead of trigger的内容。

就目前来看，在Oracle里面能满足一个语句修改多张表的，好像只有Insert All了，不知道是不是 wait4friend 坐井观天了。