这篇文章主要介绍了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来实现。

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

最新资讯
印度“失去”苹果 市场真的在滑向低端吗?

印度“失去”苹果 市

我们看印度市场的变化,不能只看表面,要看三年后、五年后
冯鑫的危机和转机

冯鑫的危机和转机

作为一名把企业做到IPO、并且在股市上创造了连续涨停
上市破发启示录:2018年互联网公司能从泡沫时代学到什么?

上市破发启示录:2018年

读史可以知兴替,为此翻出了早已落灰的旧资料,在2000年那
BAT小程序的困境,也是BAT的劣势

BAT小程序的困境,也是B

社交与支付的薄弱,是小程序面临的困境,同时也是百度与支
区块链史就是一部流氓史

区块链史就是一部流氓

比特币是《指环王》中的魔戒,不要试图挑战人性,比特币就
Siri在苹果活着、蠢着、没落着 最后的创始人也走了

Siri在苹果活着、蠢着

苹果语音助手Siri,秉持期待买进来,深受乔布斯期许,永远被
最新文章
web前端从Oracle数据库加载动态菜单所用到的数据表

web前端从Oracle数据

这篇文章主要介绍了web前端从Oracle数据库加载动态菜
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出现超出打开游标最大数的解