Oracle中merge Into的用法

简介: Oracle中merge Into的用法

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

相关文章
|
11天前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(上)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
11天前
|
SQL 存储 Oracle
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法(下)
一篇文章带你学会 Oracle 存储过程的基本介绍和高阶用法
|
9月前
|
Oracle 关系型数据库
Oracle中decode 以及ROW_NUMBER() OVER() 函数等其它相关函数用法
Oracle中decode 以及ROW_NUMBER() OVER() 函数等其它相关函数用法
83 0
|
8月前
|
SQL Oracle 关系型数据库
Oracle 中 Translate的用法
Oracle 中 Translate的用法
51 0
Zp
|
SQL Oracle 关系型数据库
Oracle sql 批量插入 Insert all into
Oracle sql 批量插入 Insert all into
Zp
306 0
|
存储 Oracle 关系型数据库
Oracle行转列函数PRINT_TABLE的用法
Oracle行转列函数PRINT_TABLE的用法
95 0
|
3天前
|
Oracle 关系型数据库 Java
实时计算 Flink版操作报错之读取Oracle数据库时遇到找不到驱动,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
实时计算 Flink版操作报错之读取Oracle数据库时遇到找不到驱动,是什么原因
|
8天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
31 3
|
11天前
|
SQL Oracle 安全
Oracle11g更改数据库名(详细教程)
Oracle11g更改数据库名(详细教程)
18 1
|
3天前
|
Oracle 关系型数据库 数据库
实时计算 Flink版产品使用合集之采集Oracle数据库时,归档日志大小暴增的原因是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。