oracle 中merge SQL语句用法

简介

MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用，MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。

概要

具体来说，MERGE语句会检查原数据表记录和目标表记录。如果记录在原数据表和目标表中均存在，则目标表中的记录将被原数据表中的记录所更新（执行Update操作）；如果目标表中不存在的某（些）记录，在原数据表中存在，则原数据表的这（些）记录将被插入到目标表中（执行Insert操作）。
在Oracle 10g之前，merge语句支持匹配更新和不匹配插入两种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。

Merge语法

MERGE [hint] INTO [schema ] table [t_alias]
USING [schema ]{ table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

代码及说明

1.MERGE INTO test T1
2.USING (SELECT OWNER , OBJECT_NAME , MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
3.ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
4.WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
5.WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);

第一行 命名目标表并给别名T1
第二行 using子句提供merge操作的数据源，命名T
第三行 on子句指定合并的条件
第四行 when matched then 子句判定条件符合则对表T1做什么改变（或删除）
第五行 when not matched then 子句判断条件不符合则插入的操作

接下来分析一个mybatis的语句

<update id="MergeToProdApply">
	merge into hec_dup_am_prod_apply a
	using (select  do.*
		   from dup_mqd_product_decl de
					left join dup_mqd_product_do do
							  on de.product_do_id = do.id
		   where do.data_status = '1'
		   and do.id in
			<foreach item="item" index="index" collection="productIds"
					 open="(" separator="," close=")">#{item}
			</foreach>
		   ) b
	on (a.prod_id = b.product_id and a.project_id = #{projectId,jdbcType=VARCHAR,mode=IN})
	when not matched then
		insert
		(id,
		 org_id,
		 org_name,
		 prod_id,
		 sync_prod_id,
		 avg_amount,
		 factory_id,
		 fac_name_f,
		 code05_id,
		 status,
		 dir_code,
		 dir_name,
		 CODE,
		 prod_type,
		 match_name_f,
		 add_type_id,
		 add_type,
		 name_cn,
		 prod_trade,
		 dosage_id,
		 sml_name_f,
		 spec_id,
		 norm_f,
		 spec_attr_id,
		 attr_name_f,
		 convert_id,
		 convert_num,
		 min_use_unit_id,
		 min_use_unit,
		 wrap_unit_id,
		 wrap_unit,
		 convert_f,
		 wrap_id,
		 warp_name_f,
		 prod_pzwh,
		 prod_zczh,
		 rbfl_id,
		 rbfl_name_f,
		 yjspec,
		 "DESCRIPTION",
		 DRUG_WAY,
		 project_id,
		 create_user,
		 create_time,
		 modify_user,
		 modify_time)
		values
		(sys_guid(),
		 b.org_id,
		 b.org_name,
		 b.PRODUCT_ID,
		 b.SYNC_PROD_ID,
		 b.AVG_AMOUNT,
		 b.FACTORY_ID,
		 b.FAC_NAME_F,
		 b.CODE05_ID,
		 '0', --
		 b.DIR_CODE,
		 b.DIR_NAME,
		 b.PROD_TYPE_CODE,
		 b.PROD_PLACE_CODE,
		 b.MATCH_NAME_F,
		 b.ADD_TYPE_ID,
		 b.ADD_TYPE,
		 b.NAME_CN,
		 b.PROD_TRADE,
		 b.DOSAGE_ID,
		 b.SML_NAME_F,
		 b.SPEC_ID,
		 b.NORM_F,
		 b.SPEC_ATTR_ID,
		 b.ATTR_NAME_F,
		 b.CONVERT_ID,
		 b.CONVERT_NUM,
		 b.MIN_USE_UNIT_ID,
		 b.MIN_USE_UNIT,
		 b.WRAP_UNIT_ID,
		 b.WRAP_UNIT,
		 b.CONVERT_F,
		 b.WRAP_ID,
		 b.WARP_NAME_F,
		 b.PROD_PZWH,
		 b.PROD_ZCZH,
		 b.RBFL_ID,
		 b.RBFL_NAME1,
		 b.YJSPEC,
		 b.DOSAGE_GEN_NAME,
		 b.DOSAGE_GEN_NAME,
		 #{projectId,jdbcType=VARCHAR,mode=IN},
		 #{createUser,jdbcType=VARCHAR,mode=IN},
		 sysdate,
		 #{createUser,jdbcType=VARCHAR,mode=IN},
		 sysdate)
    </update>

首先 命名目标表（hec_dup_am_prod_apply）并给别名 a

(select  do.*
		   from dup_mqd_product_decl de
					left join dup_mqd_product_do do
							  on de.product_do_id = do.id
		   where do.data_status = '1'
		   and do.id in
			<foreach item="item" index="index" collection="productIds"
					 open="(" separator="," close=")">#{item}
			</foreach>
		   )

using子句提供merge操作的数据源，命名b

on子句指定合并的条件 （可以理解为关联关系）

when not matched then 子句判断条件不符合则插入的操作

没有写 when matched then 表示 只要insert 不要update 或delete