MERGE INTO 目标表 USING 数据源表 ON(0=1) 的用法

存储过程中merge into 一般用于增量插入数据,如果是源表全量数据插入目标表常规认为insert into 比merge into 效率更高,

但是数据源表的数据来源是需要查询大量关联表时然后全量录入目标表时,merge into 后面的匹配条件on(0=1) 可以使插入数据效率更高;

具体的执行效率可以看完成同样事物Oracle执行时间的长短;

Oracle 9i引入的功能),其语法如下:

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 


在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数

写法如下:

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);
在Oracle 10g中MERGE有如下一些改进: 
1、UPDATE或INSERT子句是可选的 
2、UPDATE和INSERT子句可以加WHERE子句 
3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行 


我们通过实例来一一看看如上的新特性 

1. UPDATE或INSERT子句是可选的 
在9i里由于必须insert into和update都要存在,也就是不是update就是insert,不支持单一的操作,虽然还是可以曲线救国,呵呵 但是有些过于强势了。而10g里就是可选了,能符合我们更多的需求了 
比如上面的句子 
我们可以只存在update或者insert 
merge into products p using newproducts np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name 


这里,如果匹配就更新,不存在就不管了。 

2. UPDATE和INSERT子句可以加WHERE子句 
这也是一个功能性的改进,能够符合我们更多的需求,这个where的作用很明显是一个过滤的条件,是我们加入一些额外的条件,对只对满足where条件的进行更新和insert 
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name where np.product_name like 'OL%' 


这里表示只是对product_name开头是'OL'的匹配上的进行update,如果开头不是'OL'的就是匹配了也不做什么事情,insert里也可以加入where 
比如 
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name where np.product_name like 'OL%' 
when not matched then 
insert values(np.product_id, np.product_name, np.category) where np.product_name like 'OL%' 

这里注意比较一下,他们返回的结果行数,是有着差异的。 

3. 在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 


merge into products p using (select * from newproducts) np on (1=0) 
when matched then 
update set p.product_name = np.product_name 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 


这个功能一般不太用,我们的insert into本身就支持这样的功能,没有必要使用merge ,但是像插入的每条语句都需要关联复杂表查询可以使用这种语法,执行时间更短;

4. UPDATE子句后面可以跟DELETE子句来去除一些不需要的行 

delete只能和update配合,从而达到删除满足where条件的子句的纪录 
merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name delete where p.product_id = np.product_id where np.product_name like 'OL%' 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 


这里我们达到的目的就是 会把匹配的记录的prodcut_name更新到product里,并且把product_name开头为OL的删除掉。

merge into也是一个dml语句,和其他的dml语句一样需要通过rollback和commit 结束事务。



### Oracle 中 `MERGE INTO ... USING ON` 语法和用法详解 Oracle 数据库中的 `MERGE INTO` 是一种非常强大的 SQL 语句,它允许在一个 SQL 语句中根据源数据对目标表执行插入(INSERT)或更新(UPDATE)操作,有时也被称为“upsert”操作[^2]。该语句特别适用于需要将源表或子查询的数据同步到目标表的场景。 #### 基本语法结构 ```sql MERGE INTO target_table [target_alias] USING source [source_alias] ON (merge_condition) WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... [WHERE update_condition] WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...) [WHERE insert_condition]; ``` - **target_table**:要更新或插入的目标表。 - **source**:可以是表、视图或子查询,作为数据源。 - **merge_condition**:用于匹配源数据与目标表数据的条件。 - **WHEN MATCHED**:当匹配条件成立时,对目标表执行更新操作。 - **WHEN NOT MATCHED**:当匹配条件不成立时,向目标表插入新记录。 - 可选的 `WHERE` 子句可用于进一步限制更新或插入的操作范围。 #### 使用示例 假设我们有两个表:`employees_target`(目标表)和 `employees_source`(源表),结构如下: ```sql -- 目标表 CREATE TABLE employees_target ( id NUMBER PRIMARY KEY, name VARCHAR2(50), salary NUMBER ); -- 源表 CREATE TABLE employees_source ( id NUMBER PRIMARY KEY, name VARCHAR2(50), salary NUMBER ); ``` 现在我们要将 `employees_source` 表中的数据合并到 `employees_target` 表中。如果 `id` 匹配,则更新 `salary`;否则插入新的员工记录。 ```sql MERGE INTO employees_target et USING employees_source es ON (et.id = es.id) WHEN MATCHED THEN UPDATE SET et.salary = es.salary WHERE et.salary != es.salary -- 仅当薪资不同才更新 WHEN NOT MATCHED THEN INSERT (id, name, salary) VALUES (es.id, es.name, es.salary); ``` 上述语句实现了以下逻辑: - 如果 `employees_target` 表中存在与 `employees_source` 表中相同的 `id`,则只在 `salary` 不同的情况下更新其工资。 - 如果 `employees_target` 表中没有对应的 `id`,则将来自 `employees_source` 的整条记录插入进去[^3]。 #### 注意事项 1. **锁定风险**:`MERGE INTO` 操作可能会导致行级锁或表级锁,尤其是在高并发环境中,需要注意事务控制和锁竞争问题。 2. **性能优化**:对于大规模数据操作,建议在适当的时候使用索引,并避免在 `ON` 条件中使用复杂的表达式以提高查询效率。 3. **触发器行为**:如果目标表上有定义的 `AFTER UPDATE` 或 `AFTER INSERT` 触发器,它们会在相应的操作发生时被激活。 4. **日志与回滚**:确保数据库处于适当的归档模式,并考虑事务的可回滚性,以便在出现错误时能够恢复数据一致性[^1]。 #### MySQL 中的替代方案 MySQL 在较早版本中并不支持 `MERGE INTO` 语句,但可以通过 `INSERT ... ON DUPLICATE KEY UPDATE` 实现类似功能。例如: ```sql INSERT INTO employees_target (id, name, salary) SELECT id, name, salary FROM employees_source ON DUPLICATE KEY UPDATE salary = VALUES(salary); ``` 此语句会尝试将 `employees_source` 中的所有记录插入到 `employees_target` 中。如果遇到主键或唯一约束冲突(如 `id` 冲突),则执行 `UPDATE` 部分来更新已有记录[^4]。 ---
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值