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

相关内容

热门资讯

东方航空×泡泡玛特:DIMOO... 文旅观察近日,中国东方航空与泡泡玛特携手推出的DIMOO中泰建交50周年限定主题彩绘专机正式首航,执...
多个“第一”!重大突破! 今天(7月9日)上午国新办举行首场“高质量完成‘十四五’规划”系列主题新闻发布会一起看 ↓国家发展改...
中广核新能源:6月发电量140... 格隆汇7月9日|中广核新能源(1811.HK)公告,6月本集团按合并报表口径完成发电量1,403.5...
1.52亿千瓦!江苏电网最高用... 转自:上观新闻7月7日,江苏电网最高用电负荷,年内第三次刷新历史新高,达1.52亿千瓦。截至目前,南...
ST华铭因信披违规被罚,收购聚... 近日,ST华铭(维权)(300462)发布公告,近日收到中国证券监督管理委员会上海监管局下发的《行政...
山西泽辰医药乌帕替尼缓释片启动... 药物临床试验登记与信息公示平台数据显示,山西泽辰医药科技有限公司的乌帕替尼缓释片在健康受试者中随机、...
西海都市报公布地址、订阅及零售... 本报地址:西宁市长江路5号 广告经营许可证: 广告部电话:6125601 排版:西海都市报社编辑部 ...
台风丹娜丝“绘就”陆家嘴诗意画... 转自:上观新闻连日来,受台风“丹娜丝”外围环流持续影响,上海晴雨相间,同时带来降温、大风,云雾光影间...
国家发改委:中国做的这件事,全... 转自:北京日报客户端在国新办今天(9日)举行的首场“高质量完成‘十四五’规划”系列主题新闻发布会上,...
或受欧盟法规影响,苹果将在欧盟...   炒股就看金麒麟分析师研报,权威,专业,及时,全面,助您挖掘潜力主题机会! IT之家 7 月 9...