ORACLE 数据全量/增量同步 merge into 方法

最新推荐文章于 2025-06-11 09:00:00 发布
最新推荐文章于 2025-06-11 09:00:00 发布
阅读量4.1k 收藏 38
点赞数 1
CC 4.0 BY-SA版权
分类专栏： sql 文章标签： oracle 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/valkyrja110/article/details/121215821
本文介绍了如何使用Oracle的merge into语句进行数据全量和增量同步，通过实例展示了如何更新或插入数据，并探讨了删除数据的条件。主要针对两张表old和new，将new表的最新业务数据同步到old表中。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

ORACLE 数据全量/增量同步 merge into 方法

一、业务介绍

有old和new两张表，new是每天最新业务数据，old是上一天的业务数据，需要将new表中的数据更新或插入到old的主表中。

二、创建表

--创建第一张表
create table old(
				id number(10)
				,	name varchar2(20)
				,	age number(10)
);
--创建第二张表
create table new as select from old where 1 = 2;

三、准备数据

假设name字段和age字段都可能变化，需要对应id进行更新或在old表中插入新数据，我们先插入一些模拟数据。

--用存储过程插入一些数据
create or replace procedure spold(pnum in number)
is
pvid number(10);
pvname varchar2(10);
pvage number(10);
begin

    for m in 1..pnum loop
        pvid := m;
        pvname := dbms_random.string(
最低0.47元/天 解锁文章

200万优质内容无限畅学
【Oracle】Oracle中的merge into
低调做人，低调编码，神码都是浮云
07-19 5204
Oracle中的Merge Into操作
MERGE INTO 目标表 USING 数据源表 ON的用法（代码优化）
lz_N_one的博客
06-30 3151
存储过程中merge into 一般用于增量插入数据，如果是源表全量数据插入目标表常规认为insert into 比merge into 效率更高， 但是数据源表的数据来源是需要查询大量关联表时然后全量录入目标表时，merge into 后面的匹配条件on（0=1) 可以使插入数据效率更高； 具体的执行效率可以看完成同样事物Oracle执行时间的长短； Oracle 9i引入的功能），其语法如下： MERGE INTO a_crt_repay_loan t --增量切片 USING (SELECT p_da
transferdb:Transferdb支持异步数据库模式转换，全量数据导入，导入和增量数据同步功能（Oracle数据库-> MySQL数据库）
03-19
Transferdb transferdb用于分布式数据库迁移（Oracle数据库-> MySQL数据库），现阶段支持的功能（原transferdb版本被重构）： 支持表结构定义转换 考虑到Oracle分区表特殊且MySQL数据库复杂分区可能不支持，分区表统一认为普通表转换，但是reverse阶段日志中会打印警告【分区表】，若有要求，建议反向之后检查，需手工转换 支持自定义配置表分段类型规则转换（表->模式->内置） 支持规范配置规则转换 支持表索引创建 支持非空约束，外键约束，检查约束等 数据同步【数据同步需要存在主键或者唯一键】 FULL模式【全量数据导入导入】数据同步转换导入要求表中存在主键或唯一键，否则因异常错误退出或手工中断退出，断点续传【替换为】无法替换，数据可能会导致重复【除非手工清理下游重新导入】 ALL模式【全量导出导入+增量数据同步】 增量基于logminer日志
在两个ORACLE数据库之间实现数据增量同步
冷月宫主的专栏
11-27 1万+
在两个数据库中实现数据增量同步，令数据库之间的数据能够同步更新。 Oracle数据库IP：192.168.0.1（源库）、192.168.0.2（目标库） 1、在源库创建测试表TEST create table TEST(  ID          NUMBER not null,  NAME        VARCHAR2(200)); 2、插入一条数据 INSER
Oracle全量同步与增量同步
雷神乐乐的博客
06-11 944
Oracle全量同步与增量同步
GBase RTSync产品Oracle-＞ Oracle 增量数据同步
zhu1981hui的博客
05-26 218
Oracle-> Oracle 增量数据同步 1、库、表、列级别映射的增量同步 2、支持断点续传，保证异常场景数据丌丢失 3、支持多个 Oracle 数据源 4、支持高可用
oracle全量增量_数据同步：全量与增量
weixin_39890327的博客
12-19 2692
1.背景数据如果保留多份，就会存在一致性问题，就需要同步，同步分为两大类：全量和增量2. 概述数据如果要保留副本，要么同时写(就是多写)，或者进行复制：异步写(即从主数据拷贝到副本)；同时写(多写)，引出一个问题，写多少节点算成功(场景：分布式系统)？全部写成功才算成功，还是写大多数成功算成功，还是写指定几个节点算成功？异步写的话，如果采用异步复制，那么实时性需要考量的话，就需要采用性能优先的架构...
Oracle -- 增量同步数据_游标和merge into 方式同步
lzj033的专栏
01-29 1907
增量抽取 只需抽取新增的或修改的数据。此方法性能好，但容易遗漏。 目标表中有，但是源表中没有的话，更新不了。 以时间戳取增量，对源表删除的数据无能为力。 通过源表更新目标表的时候，通常是先判断 源表中的数据在目标表中是否存在（通过主键判断） 如果存在，那么就用源表的数据，更新目标表的数据。 如果不存在，那么就直接从源表中插入数据到目标表。 ------------------------...
java使用datax增量同步代码
04-19
Java 使用 DataX 进行增量同步是大数据领域中常见的数据迁移任务，DataX 是阿里开源的一个强大、高效的数据同步工具，它可以实现不同数据存储之间的数据迁移。本篇将详细讲解如何在 Java 项目中利用 DataX 实现增量...
Oracle增量数据同步、强制结束正在运行SQL
10-10
#### Oracle增量数据同步方法 在Oracle数据库中，实现增量数据同步可以通过以下几种方式： 1. **触发器（Triggers）**：通过创建触发器监控源表中的数据变化，并将变化的数据插入到目标表中。 2. **日志挖掘（Log ...
增量同步Oracle数据库，游标和merge into 方式
weixin_35833574的博客
10-20 1909
这里写自定义目录标题增量同步Oracle数据库，游标和merge into 方式使用游标进行增量数据同步使用 merge into 的方式实现增量同步 增量同步Oracle数据库，游标和merge into 方式 增量抽取 只需抽取新增的或修改的数据。此方法性能好，但容易遗漏。 目标表中有，但是源表中没有的话，更新不了。 以时间戳取增量，对源表删除的数据无能为力。 通过源表更新目标表的时候，通常是先判断 源表中的数据在目标表中是否存在（通过主键判断） 如果存在，那么就用源表的数据，更新目标表的数据。 如果不
oracle实现数据同步
02-14
巧用dblink结合oracle快照实现两台服务器的数据同步 有详细步骤
利用ogg实现oracle到kafka的增量数据实时同步
xusanyao的博客
07-07 4448
前言ogg即Oracle GoldenGate是Oracle的同步工具，本文讲如何配置ogg以实现Oracle数据库增量数据实时同步到kafka中，其中同步消息格式为json。 下面是我的源端和目标端的一些配置信息：-版本OGG版本ip别名源端OracleRelease 11.2.0.1.0Oracle GoldenGate 11.2.1.0.3 for Oracle on Linux x86-6...
Oracle通过函数调用dblink同步表数据方案（全量/增量）
JangBingYang的博客
07-13 1410
通过函数调用dblink的方式同步两个异地Oracle数据库表的数据
oracle 存储过程——增量
Empty_I的博客
10-31 708
ORALCE 存储过程 增量
DataX oracle同步mysql(全量和增量)
热门推荐
zhuyu19911016520
04-13 1万+
datax sync oracle to mysql
oracle增量条件导入导出dmp,Oracle数据导入 导出
weixin_35890888的博客
04-03 450
导出表:exp scott/tiger@mycon tables=(dept,emp) file=tab1.dmp导出用户:exp system/manager@mycon owner=scott file=usr1.dmp导出数据库:1.完全导出exp system/manager@mycon full=y inctype=complete file=full1.dmp2.增量导出exp sys...
oracle 增量导出 导入,Oracle增量导入导出
weixin_39903477的博客
04-11 1079
增量导出/导入增量导出是一种常用的数据备份方法，它只能对整个数据库来实施，并且必须作为SYSTEM来导出。在进行此种导出时，系统不要求回答任何问题。导出文件名缺省为export.dmp，如果不希望自己的输出文件定名为export.dmp，必须在命令行中指出要用的文件名。增量导出包括三种类型:(1)、“完全”增量导出(Complete)即备份三个数据库，比如:exp system/manager i...
oracle中with as 函数在merge into中的应用
最新发布
07-27
<think>首先，用户的问题是关于Oracle数据库的：他想知道如何在Oracle中使用WITH AS子句结合MERGE INTO语句。我需要参考提供的引用来构建我的回答。 回顾引用： - 引用[1]：解释了MERGE INTO的功能，作为upsert操作（update + insert）。 - 引用[2]：详细说明了MERGE INTO的使用，包括匹配时更新，不匹配时插入。 - 引用[3]：提到了WITH AS、MERGE INTO的结合，给出了一个示例语法：`merge into table_a a using table_b b on (a.id = b.id) when matched then update set a.name = b.name where …… when not matched then insert values (a.id,a.name) where ……`。 - 引用[4]：讨论了Oracle的DML操作，包括MERGE INTO和RETURNING子句，但未直接涉及WITH AS。 用户的具体需求是：使用WITH AS子句结合MERGE INTO。引用[3]直接提到了这个，所以我应该重点参考那个。 现在，构建我的回答： 1. **结构清晰**：我需要逐步解释。首先，解释WITH AS是什么，然后MERGE INTO是什么，最后如何结合使用。 2. **使用中文**：整个回答必须是中文。 3. **LaTeX格式**：对于行内数学表达式，使用$...$；对于独立公式，使用$$...$$并单独成段。但在这个上下文中，可能没有数学表达式，除非涉及计算，但这里是数据库查询，所以可能不需要。如果有变量或SQL片段，我会用代码块或正常文本处理。 4. **生成相关问题**：在回答末尾，添加几个相关问题，使用§§相关问题§§格式。 5. **引用标识**：在回答中引用的段落末尾自然地添加引用标识，例如[^1]。由于引用是站内的，我需要根据内容引用。例如，引用[3]直接相关。 关键点： - WITH AS子句：用于创建临时结果集，在查询中引用。 - MERGE INTO：用于合并数据，基于条件更新或插入。 结合使用：在MERGE INTO的USING子句中使用WITH AS定义的查询。 从引用[3]：`merge into table_a a using table_b b on (a.id = b.id) ...`。这里，table_b可以是WITH AS定义的结果。 所以，示例：使用WITH AS定义一个临时表，然后在MERGE INTO中引用它。 步骤： 1. 解释WITH AS：这是一个公共表表达式（CTE），允许定义临时结果集。 2. 解释MERGE INTO：用于执行upsert操作。 3. 结合方法：在MERGE INTO的USING子句中使用CTE。 4. 提供示例代码：基于引用[3]的提示。 示例代码： ```sql WITH temp_data AS ( SELECT id, name FROM source_table WHERE condition ) MERGE INTO target_table t USING temp_data s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name); ``` 确保语法正确：在Oracle中，WITH AS用于定义CTE，然后可以跟MERGE INTO。 引用[4]提到使用绑定变量和批量操作，但用户没问这个，所以可能不相关。 回答结构： - 引言：简要介绍主题。 - 解释WITH AS子句。 - 解释MERGE INTO语句。 - 如何结合使用：描述步骤。 - 提供完整示例。 - 结尾：总结好处。 然后，生成相关问题。 最后，添加引用：在相关部分末尾添加[^n]，例如，当提到MERGE INTO功能时引用[1]或[2]，当提到结合使用时引用[3]。 相关问题： 1. 如何优化MERGE INTO的性能？ 2. WITH AS子句的其他应用场景？ 3. MERGE INTO的错误处理？ 确保回答真实可靠：基于Oracle文档或标准实践。</think>### Oracle数据库中WITH AS子句与MERGE INTO语句结合使用方法 在Oracle数据库中，`WITH AS`子句（也称为公共表表达式，CTE）用于创建临时结果集，而`MERGE INTO`语句则实现高效的“upsert”操作（即更新或插入数据）。结合使用两者，可以简化复杂的数据合并任务，尤其适用于需要基于动态查询结果进行数据同步的场景。以下我将逐步解释其原理、语法和实际应用方法。 --- ### 1. **理解核心组件** - **`WITH AS`子句**： 它允许定义一个或多个临时结果集（称为CTE），这些结果集在后续查询中可被多次引用。这有助于提高代码可读性和复用性，避免重复子查询。例如： ```sql WITH temp_data AS ( SELECT id, name FROM employees WHERE department = 'IT' ) SELECT * FROM temp_data; ``` 此CTE定义了一个临时表`temp_data`，仅包含IT部门员工的数据。 - **`MERGE INTO`语句**： 该语句基于源表和目标表的关联条件，执行条件更新或插入操作。如果匹配则更新目标表记录，否则插入新记录。其核心功能是高效处理数据合并，减少多次DML操作的开销[^2]。基本语法如下： ```sql MERGE INTO target_table t USING source_table s ON (t.id = s.id) WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name); ``` 此示例中，`source_table`作为数据源，与`target_table`通过`id`字段匹配，实现更新或插入。 --- ### 2. **结合使用的步骤与方法** 将`WITH AS`与`MERGE INTO`结合的关键在于：**在`MERGE INTO`的`USING`子句中直接引用CTE定义的临时结果集**。这样，CTE生成的动态数据可作为`MERGE INTO`的源表。以下是详细步骤： #### **步骤说明** 1. **定义CTE**：使用`WITH`语句创建临时结果集，例如基于复杂查询、过滤或聚合生成数据。 2. **在`MERGE INTO`中引用CTE**：将CTE名称作为`USING`子句的源表。 3. **指定关联条件**：在`ON`子句中定义匹配逻辑（如主键或唯一键）。 4. **处理匹配与不匹配情况**：在`WHEN MATCHED`和`WHEN NOT MATCHED`子句中定义更新或插入操作。 5. **可选添加条件**：在`UPDATE`或`INSERT`后使用`WHERE`子句进一步过滤操作范围。 #### **注意事项** - **语法顺序**：CTE必须在`MERGE INTO`之前定义，且整个语句需作为一个单一SQL块执行。 - **性能优化**：确保`ON`子句的关联字段有索引，以避免全表扫描。对于大数据量，可考虑批量操作（如引用[4]提到的绑定变量或PL/SQL批量DML）[^4]。 - **错误处理**：添加`WHERE`条件可防止误操作，但需注意事务一致性（建议在测试环境验证后执行）。 --- ### 3. **完整示例** 假设场景：需要将新员工数据（来自临时查询）同步到员工表`employees`中。如果员工ID存在则更新姓名，否则插入新记录。以下是结合`WITH AS`和`MERGE INTO`的示例代码： ```sql -- 定义CTE：生成临时数据源 WITH new_employees AS ( SELECT employee_id AS id, first_name || ' ' || last_name AS full_name FROM external_hire -- 外部招聘表 WHERE hire_date > DATE '2023-01-01' -- 仅处理2023年后入职的员工 ) -- 结合MERGE INTO执行数据合并 MERGE INTO employees t -- 目标表：员工表 USING new_employees s -- 源表：CTE定义的临时结果集 ON (t.employee_id = s.id) -- 关联条件：员工ID匹配 WHEN MATCHED THEN UPDATE SET t.full_name = s.full_name WHERE t.status = 'Active' -- 仅更新状态为Active的记录 WHEN NOT MATCHED THEN INSERT (employee_id, full_name, status) VALUES (s.id, s.full_name, 'New'); -- 插入新记录并设置状态 ``` #### **示例解析** - **CTE部分**：`new_employees` CTE从`external_hire`表查询2023年后入职的员工，并生成`id`和`full_name`字段。 - **MERGE INTO部分**： - `USING new_employees s`：将CTE作为源数据。 - `ON (t.employee_id = s.id)`：通过员工ID匹配目标表和源数据。 - `WHEN MATCHED`：匹配时更新`full_name`，但仅当员工状态为`Active`时执行。 - `WHEN NOT MATCHED`：不匹配时插入新记录，包括`employee_id`、`full_name`和默认状态`New`。 - **优点**：此方法避免了多次查询，同时利用CTE处理动态数据源，提升代码可维护性。 --- ### 4. **常见应用场景与最佳实践** - **应用场景**： - **数据仓库ETL**：将临时存储的数据（如CTE生成的聚合结果）合并到目标表。 - **实时数据同步**：从日志表或外部接口动态获取数据后，立即更新主表。 - **批量数据处理**：结合CTE的过滤能力，仅处理增量数据（如引用[3]所示）[^3]。 - **最佳实践**： - **性能优化**：在CTE中使用高效查询（如限制返回列），并为关联字段添加索引。引用[4]建议使用绑定变量减少硬解析[^4]。 - **事务控制**：在执行`MERGE INTO`前启用事务（`BEGIN`），并在完成后提交（`COMMIT`），以防部分失败。 - **错误调试**：添加`LOG ERRORS`子句捕获错误（Oracle 10g+支持），例如： ```sql MERGE INTO employees ... LOG ERRORS INTO err$_employees REJECT LIMIT UNLIMITED; ``` --- ### 总结 在Oracle中，结合`WITH AS`和`MERGE INTO`能高效处理基于动态查询的数据合并任务。核心方法是将CTE作为`MERGE INTO`的源表，通过关联条件实现更新或插入。此方法不仅简化代码，还提升性能，尤其适合大数据量同步场景[^2][^3]。实际使用时，务必测试关联逻辑和条件过滤，以确保数据准确性。
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值