这篇文章主要介绍了oracle中merge into用法及实例解析,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧

merge into的形式:

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...) 
WHEN MATCHED THEN 
  [UPDATE sql] 
WHEN NOT MATCHED THEN 
  [INSERT sql] 

作用:判断B表和A表是否ON中条件,如果则用B表去更新A表,如果不,则将B表数据插入A表但是有很多可选项,如下:

1.正常模式

2.只update或者只insert

3.带条件的update或带条件的insert

4.全插入insert实现

5.带delete的update(觉得可以用3来实现)

下面一一测试。

测试建以下表:

create table A_MERGE 
( 
 id  NUMBER not null, 
 name VARCHAR2(12) not null, 
 year NUMBER 
); 
create table B_MERGE 
( 
 id  NUMBER not null, 
 aid NUMBER not null, 
 name VARCHAR2(12) not null, 
 year NUMBER, 
 city VARCHAR2(12) 
); 
create table C_MERGE 
( 
 id  NUMBER not null, 
 name VARCHAR2(12) not null, 
 city VARCHAR2(12) not null 
); 
commit; 

其表结构截图如下图所示:

A_MERGE表结构:


B_MERGE表结构


C_MERGE表结构


1.正常模式

先向A_MERGE和B_MERGE插入测试数据:

insert into A_MERGE values(1,'liuwei',20); 
insert into A_MERGE values(2,'zhangbin',21); 
insert into A_MERGE values(3,'fuguo',20); 
commit; 
 
insert into B_MERGE values(1,2,'zhangbin',30,'吉林'); 
insert into B_MERGE values(2,4,'yihe',33,'黑龙江'); 
insert into B_MERGE values(3,3,'fuguo',,'山东'); 
commit; 

此时A_MERGE和B_MERGE表中数据截图如下:

A_MERGE表数据:


B_MERGE表数据:


然后再使用merge into用B_MERGE来更新A_MERGE中的数据:

MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID) 
WHEN MATCHED THEN 
 UPDATE SET A.YEAR=C.YEAR  
WHEN NOT MATCHED THEN 
 INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); 
commit; 

此时A_MERGE中的表数据截图如下:


2.只update模式

首先向B_MERGE中插入两个数据,来为了体现出只update没有insert,必须有一个数据是A中已经存在的

另一个数据时A中不存在的,插入数据语句如下:

insert into B_MERGE values(4,1,'liuwei',80,'江西'); 
insert into B_MERGE values(5,5,'tiantian',23,'河南'); 
commit; 

此时A_MERGE和B_MERGE表数据截图如下:

A_MERGE表数据截图:


B_MERGE表数据截图:


然后再次用B_MERGE来更新A_MERGE,但是仅仅update,没有写insert部分。

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) 
WHEN MATCHED THEN 
 UPDATE SET A.YEAR=C.YEAR; 
  
commit; 

merge完之后A_MERGE表数据截图如下:可以发现仅仅更新了AID=1的年龄,没有插入AID=4的数据


3.只insert模式

首先改变B_MERGE中的一个数据,因为上次测试update时新增的数据没有插入到A_MERGE,这次可以用。

update B_MERGE set year=70 where AID=2; 
commit; 

此时A_MERGE和B_MERGE的表数据截图如下:

A_MERGE表数据:


B_MERGE表数据:


然后用B_MERGE来更新A_MERGE中的数据,此时只写了insert,没有写update:

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) 
WHEN NOT MATCHED THEN 
  insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); 
commit; 

此时A_MERGE的表数据截图如下:


4.带where条件的insert和update。

我们在on中进行完条件匹配之后,还可以在后面的insert和update中对on筛选出来的记录再做一次条件判断,用来控制哪些要更新,哪些要插入。

测试数据的sql代码如下,我们在B_MERGE修改了两个人名,并且增加了两个人员信息,但是他们来自的省份不同,

所以我们可以通过添加省份条件来控制哪些能修改,哪些能插入:

update B_MERGE set name='yihe++' where id=2; 
update B_MERGE set name='liuwei++' where id=4; 
insert into B_MERGE values(6,6,'ningqin',23,'江西'); 
insert into B_MERGE values(7,7,'bing',24,'吉安'); 
commit; 

A_MGERGE表数据截图如下:


B_MERGE表数据:


然后再用B_MERGE去更新A_MERGE,但是分别在insert和update后面添加了条件限制,控制数据的更新和插入:

merge into A_MERGE A USING (select B.AID,B.name,B.year,B.city from B_MERGE B) C  
ON(A.id=C.AID)  
when matched then 
 update SET A.name=C.name where C.city != '江西' 
when not matched then 
 insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city='江西'; 
commit; 

此时A_MERGE截图如下:


5.无条件的insert。

有时我们需要将一张表中所有的数据插入到另外一张表,此时就可以添加常量过滤谓词来实现,让其只匹配和不匹配,这样就只有update或者只有insert。这里我们要无条件全插入,则只需将on中条件设置为永假即可。用B_MERGE来更新C_MERGE代码如下:

merge into C_MERGE C USING (select B.AID,B.NAME,B.City from B_MERGE B) C ON (1=0) 
when not matched then 
 insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City); 
commit; 

C_MERGE表在merge之前的数据截图如下:


B_MERGE数据截图如下:


C_MERGE表在merge之后数据截图如下:


6.带delete的update

MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. 
DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.

但我觉得这个带where条件的update差不多,都是控制update,完全可以用带where条件的update来实现。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持爱安网。

最新资讯
苹果或曾计划发布金色款iPhone X

苹果或曾计划发布金色

据该文件送审的时间推测,苹果本来计划在去年九月份发布
从什么时候起,你玩游戏开始考虑时间成本了?

从什么时候起,你玩游戏

于是,虽然现在游戏已经是消费成本最低的娱乐之一,但玩游
印度创企Milkbasket,靠什么在杂货电商领域脱颖而出

印度创企Milkbasket,靠

当你打开冰箱发现第二天需要喝的牛奶已经没有了或者你
中美贸易战升级,对哪国CPI影响更大?

中美贸易战升级,对哪国

就通胀影响而言,特朗普挑起的中美贸易战如果全面开打,真
美团买摩拜买的是什么?

美团买摩拜买的是什么

美团买的是街头的自行车吗?
Facebook 深陷泥潭,扎克伯格打不好的广告牌

Facebook 深陷泥潭,扎

每一个在互联网世界里走过一遭的人,谁都别想全身而退。
最新文章
oracle中merge into用法及实例解析

oracle中merge into用

这篇文章主要介绍了oracle中merge into用法及实例解析
WINDOWS下使用DOS命令行连接oracle数据库

WINDOWS下使用DOS命令

本文讲述了通过windows下的DOS命令连接oracle数据库并
Windows10 x64安装、配置Oracle 11g过程记录(图文教程)

Windows10 x64安装、

这篇文章主要介绍了Windows10 x64安装、配置Oracle 11
Oracle数据库空间满了进行空间扩展的方法

Oracle数据库空间满了

这篇文章主要介绍了Oracle数据库空间满了进行空间扩展
Oracle出现超出打开游标最大数的解决方法

Oracle出现超出打开游

这篇文章主要介绍了Oracle出现超出打开游标最大数的解
Oracle存储过程循环语法实例分析

Oracle存储过程循环语

这篇文章主要介绍了Oracle存储过程循环语法,结合实例