Oracle 9i引入了MERGE功能,它使用户可以在一个SQL语句中对条件匹配的元组进行更新操作,无法匹配的进行插入操作。
在Oracle 10g中MERGE功能得到了进一步的增强,例如,UPDATE或INSERT子句是可选的;UPDATE和INSERT子句可增加WHERE子句;ON条件中使用的常量过滤谓词来INSERT所有的行到目标表中,而不需要连接源表和目标表;UPDATE子句后面可以跟DELETE子句去除一些不需要的行等。由于MERGE提供了在一个SQL语句中实现INSERT和UPDATE等功能,降低了开发人员在编写此类应用的代码量,提高了开发效率,相对于应用层的实现在性能上也具备一定的优势。
从SQL Server 2008开始也对MERGE功能提供了支持。该功能PostgreSQL和MySQL目前尚未提供支持。
下面向大家详细介绍一下红象数据库的MERGE功能,包括语法、示例等。
语法
MERGE:
MERGE INTO [schema.]{ table | view } [t_alias]
USING {[schema.] { table | view } | subquery} [t_alias]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
merge_update_cluase:
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT} [, column = { expr | DEFAULT}] …
[ where_clause ]
[ DELETEwhere_clause ]
merge_insert_clause:
WHEN NOT MATCHED THEN
INSERT [(column[, column]…)]
VALUES ({ expr | DEFAULT}[, {expr | DEFAULT}]…)
[ where_clause ]
where_clause:
WHERE condition
语法图如下:
MERGE:
merge_update_clause:
merge_insert_clause:
where_clause:
解释说明INTO子句
指明要更新或者插入的目标表或视图,其中视图必须为可更新视图。USING子句
USING子句指定要更新或插入的源表、视图或子查询。ON子句
ON子句指定MERGE操作更新或插入的条件。如果目标表有匹配连接条件的元组,则更新该元组;如果没有匹配,则向目标表插入源表的数据。
对于ON中条件指定的属性(列),不能在后面的更新语句中进行更新。merge_update_clause子句
当目标表和源表的ON条件操作为真时,执行此语句,即更新目标表数据。该更新操作会触发目标表上的触发器。更新的列不能是ON条件中被引用的列,更新时可以通过WHERE条件指明要更新的元组,条件中既可以包含源表的列,也可以包含目标表的列,当指明WHERE条件且条件为假时,则不更新。
执行UPDATE时,源表中的匹配的元组要保证唯一,否则会报错。
目标表如果是视图,更新时不能使用DEFAULT值。
merge_update_clause子句可以包含一个DELETE子句,负责删除目标表和源表的ON条件为真的元组,DELETE子句不影响INSERT插入的元组。删除子句作用于更新后的元组上,既可以和源表相关,也可以和目标表相关,或者两者都相关。如果ON条件为真,但是不符合更新条件,并没有更新数据,那么DELETE将不会删除任何数据。当执行了删除操作,会触发目标表上的DELETE触发器,也会进行约束检查。merge_insert_clause子句
当目标表和源表的ON条件为假时,执行该语句。同时会触发目标表上的INSERT触发器,也会进行约束检查。可以指定插入条件,插入时的WHERE条件中只能引用源表中的列。VALUES后面也只能引用源表中的列,不能包含目标表的列。
目标表是视图时,插入时不能使用DEFAULT值。
权限
需要有对源表的READ/SELECT权限,对目标表的UPDATE/INESRT权限,如果merge_update_clause中包括DELETE子句,还需要有目标表的DELETE权限。
MERGE对触发器的支持MERGE支持语句级触发器
触发器执行的顺序总是按照INSERTUPDATEDELETE的顺序来执行。
触发器执行的依赖条件取决于MERGE中是否包含与触发器中所定义的触发器操作(INSERT/UPDATE/DELETE)相对应的子句;若包含则无论子句是否执行,触发器都会被触发执行,否则将不会执行。MERGE支持行级触发器
触发器的执行顺序总是和触发的事件中对应子句的执行顺序一致。
触发器执行的依赖条件取决于MERGE中是否执行了与触发器所定义触发事件(INSERT/UPDATE/DELETE)相对应的子句;若执行则触发器会执行,否则不会执行。MERGE不支持INSTEAD OF触发器
示例准备测试数据
MERGE仅执行UPDATE操作
MERGE仅执行INSERT操作
插入指定列
USING子句中使用子查询
MERGE支持语句级触发器
对于MERGE中没有出现的子句,相对应的子句的触发器将不被触发,例如:
MERGE支持行级触发器
准备环境:
创建行级触发器:
执行MERGE语句并查看结果:
以上是红象数据库在PostgreSQL的基础上实现的MERGE功能,与Oracle的Merge功能基本兼容。
https://gitee.com/redelephant2020