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

最新推荐文章于 2025-07-22 09:44:44 发布

原创最新推荐文章于 2025-07-22 09:44:44 发布 · 2.7w 阅读

42 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #存储过程 #merge into

数据库专栏收录该内容

12 篇文章

订阅专栏

存储过程中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 结束事务。