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表中!!!

相关内容

热门资讯

村口走来了一老一小两个人,老人... 村口走来了一老一小两个人,老人牵着小孩,小孩搀扶着老人,续写?村口的道路弯曲而宽阔,阳光洒在老人和小...
小猴子下山的故事寓意告诉我们什... 小猴子下山的故事寓意告诉我们什么道理,小猴子下山的故事寓意 做事应该1.小猴子下山的故事告诉人们人有...
为什么大家都说余华<兄弟... 为什么大家都说余华<兄弟>下写D不好呢?就是因为那些情节的描写么?我觉得没什么啊~写的又不是很深,都...
有一本网络小说的男主角叫李木,... 有一本网络小说的男主角叫李木,问一下书名和作者奇迹上面搜索 《侠客李木的一生》,仙侠类型,有点武侠的...
大学生创业开干洗店怎么样 加盟... 现在创业的很多,而大学生创业更是多,毕竟国家也在支持大学生创业,甚至还有大学生创业零利息贷款,这都是...
干洗加盟店年轻人创业的首选项目... 渠道建设全营销干洗加盟行业的年轻人如何赢得创业的首选项目呢?对于大学生来说,走出象牙塔的年轻人来说,...
适合山区的创业项目有哪些 适合...   适合山区的创业项目——新高山葡萄该品种为高山葡萄变异株中选育出来的新株系。目前农村种植什么最收益...
很多公司不要35岁以上的员工,... 很多公司不要35岁以上的员工,这是为什么?为了公司的效率考虑,一般人35岁以后精力跟不上公司的发展节...
100万钓具招商创业致富项目 ... 一、特色农家乐致富的好项目,伴随着生活水平的提高与交通条件的改善,郊区游越来越受到城市人的青睐,以“...
湖南千傲渔具有限公司让您轻松创... 钓具领域所显示的创业空间很巨大,市场备受关注和喜欢的品牌也有不少,投资者都想掌握好的创业项目,干一番...