Oracle中merge Into的用法
创始人
2024-05-29 06:23:24
0

Oracle中merge Into的用法

使用场景

在操作数据库时,数据存在的情况下,进行update操作;不存在的情况下,进行insert操作;在Oracle数据库中,能够使用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……               -- 关联条件进行插入操作

演示示例

为了演示,下面提供了两张测试表以及数据:

-- 测试表(1) tmp 
create table tmp
(id               VARCHAR2(20) not null,tmp_name        VARCHAR2(120),tmp_date  VARCHAR2(8),is_delete        VARCHAR2(1),creator          VARCHAR2(24),created_at       NUMBER(20),updater          VARCHAR2(24),updated_at       NUMBER(20)
)
-- 测试表(2) temp
create table temp
(id               VARCHAR2(20) not null,tmp_id        VARCHAR2(20),temp_name  VARCHAR2(120),temp_date  VARCHAR2(8),is_delete        VARCHAR2(1),creator          VARCHAR2(24),created_at       NUMBER(20),updater          VARCHAR2(24),updated_at       NUMBER(20)
)
-- 测试数据
truncate table tmp;
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00001', 'tmp测试数据1', '20220628', '0', 'admin', null, null, null);
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00002', 'tmp测试数据2', '20221223', '0', 'admin', null, null, null);
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00003', 'tmp测试数据3', '20210927', '0', 'admin', null, null, null);
truncate table temp;
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('E74C9EC', '00001', 'temp测试数据1', '20210823', '0', 'admin', null, null, null);
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('39978FC', '00002', 'temp测试数据2', '20211012', '0', 'admin', null, null, null);
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('88640EF', '00006', 'temp测试数据3', '20211121', '0', 'admin', null, null, null);
commit;

tmp表数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pmolJugI-1677982321930)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304152805601-16779148878521.png)]

temp表数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lryP65OA-1677982321931)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304152832175.png)]

两表的关联关系

select t.*,tt.* from tmp t,temp tt where t.id = tt.tmp_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kqkekNP8-1677982321932)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304153002916-16779150044203.png)]

merge into示例:

merge into temp  t   
using  tmp tt on (t.tmp_id = tt.id)
when matched then           update set t.temp_name ='xxkfz' where t.tmp_id = '00001'delete where (t.tmp_id  =  '00002')
when not matched then    insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);commit;

几点说明:

  • 被更新的表写在merge into之后
  • 更新来源数据表写在using之后,并将相关字段查询出来,为查询结果定义别名
  • on 之后表示更新满足的条件
  • when matched then:表示当满足条件时要执行的操作
  • update set: 被更新表.被更新字段 = 更新表.更新字段—此更新语句不同于常规更新语句
  • when not matched then:表示当不满足条件时要执行的操作。
  • insert (被更新表.被更新字段,…) values (更新表.更新字段,…)
  • commit:表示提交事务

执行完成以上语句后,结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-31VFYQxq-1677982321932)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304153734908-16779154570755.png)]

发现:temp表中tmp_id为00002的数据没有被删除。

why???

因为:如果更新和删除同时存在,删除的条件一定要在更新的条件内,否则数据不能删除!!!

为了更好的测试该场景,修改如下:

merge into temp  t    
using  tmp tt on (t.tmp_id = tt.id)
when matched then              update set t.temp_name ='xxkfz' where t.tmp_id  in ('00001','00002')delete where (t.tmp_id  =  '00002')
when not matched then    insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);commit;

再次重置两表测试数据,执行以上语句,结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2AI5Dvhx-1677982321933)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304154721686-16779160431659.png)]

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz;tmp_id为00002的数据成功删除!!!

对于没有匹配的数据:tmp表id为00003的数据也成功插入带temp表中!!!

5Dvhx-1677982321933)]

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz;tmp_id为00002的数据成功删除!!!

对于没有匹配的数据:tmp表id为00003的数据也成功插入带temp表中!!!

相关内容

热门资讯

这些日常用语竟都源自戏曲 转自:贵州日报京剧《穆桂英挂帅》剧照。 新华/传真 戏曲文化以其独特的魅力深深吸引着...
美国得州洪水死亡人数升至80人 转自:贵州日报 新华社休斯敦7月6日电 美国得克萨斯州政府和地方政府6日说,截至当天下午,得...
第三家股份行AIC来了 招银金... 每经记者 赵景致 每经编辑 陈 星  近日,招商银行发布公告称,已收到《国家金融监督管理总局关于筹建...
求男主男友力爆棚的小说,不要太... 求男主男友力爆棚的小说,不要太玛丽苏,多一点哦宦妃天下?纨绔世子妃?
求古代小说名和男女主名,女主名... 求古代小说名和男女主名,女主名字里要有个嫣字,内容是两个会武功的求古代小说名和男女主名,女主名字里要...
谁有资格被称为“进攻万花筒”呢... 谁有资格被称为“进攻万花筒”呢?安东尼,皮尔斯,杜兰特,科比,麦迪,艾弗森,乔丹,哈登。安东尼有资格...
四川国光农化股份有限公司关于使... 证券代码:002749 证券简称:国光股份 公告编号:2025-042号四川国光农化股份有限公司关...
最近什么书好看 最近什么书好看最近好像书慌啊!感觉没什么好书!大家给我推荐个!最好是年前刚写的或今年写的!要不我感觉...
莫要遇事躲着走 ●荣庐 鸵鸟一遇到危险,就会把头埋入沙中自以为安全。而在现实中,有的干部就如鸵鸟一般,见事儿就...
上海城地香江数据科技股份有限公... 证券简称:城地香江 证券代码:603887 公告编号:2025-079上海城地香江数据科技股份有限...