阅读量:0
Oracle merge into 语句用法 Oracle merge into 批量更新 关联更新 批量修改 关联修改
一、概述
在开发任务中，遇到一个需求，同一批次的名单；根据一定的条件判断是否存在，若存在，则进行更新操作；若不存在，进行插入操作。实现方法有两种：
1. java代码中，使用业务逻辑来判断是否存在，存在，修改；不存在，添加。
2、由于使用的Oracle数据库，可以使用merge into语句来实现批量的添加、修改操作
本文将记录和讲解merge into 语句的使用 和 mybatis中使用 merge into 语句 。
二、代码示例
1、 merge into 语法规则
MERGE INTO target_table tt -- 目标表 USING source_table st -- 关联表 ON (tt.id = st.id AND tt.age = st.age ) -- 是否唯一条件，可以是多个 WHEN MATCHED AND tt.name <> st.name THEN -- 1、满足条件--带额外条件 WHEN MATCHED THEN -- 2、满足条件-- 不带额外条件 UPDATE SET -- 执行更新操作，注意没有 表名 update set tt.name = st.name , tt.age = st.age [ WHEN NOT MATCHED THEN ] -- 3、不满足条件, 可选，非必须语句 INSERT (id, name) VALUES (st.id, st.name); -- 执行添加操作，注意没有表名 insert 2.1、创建2张表
-- WUDI.USER_TARGET definition CREATE TABLE "WUDI"."USER_TARGET" ( "ID" VARCHAR2(100), "NAME" VARCHAR2(100), "ADDR" VARCHAR2(100) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "WUDI" ; COMMENT ON COLUMN WUDI.USER_TARGET.ID IS '主键'; COMMENT ON COLUMN WUDI.USER_TARGET.NAME IS '名字'; COMMENT ON COLUMN WUDI.USER_TARGET.ADDR IS '地址'; --- USER_SOURCE 表创建过程略2.2、插入数据
-- 1.1、 USER_TARGET 初始化数据 ID= 1,2,3 INSERT INTO WUDI.USER_TARGET(ID, NAME, ADDR) VALUES('1', 'TARGET=1', ''); INSERT INTO WUDI.USER_TARGET(ID, NAME, ADDR) VALUES('2', 'TARGET=2', ''); INSERT INTO WUDI.USER_TARGET(ID, NAME, ADDR) VALUES('3', 'TARGET=3', NULL); -- 2.1、 USER_SOURCE 初始化数据 ID= 3,4,5 INSERT INTO WUDI.USER_SOURCE (ID, NAME, ADDR) VALUES('3', 'SOURCE=3', NULL); INSERT INTO WUDI.USER_SOURCE (ID, NAME, ADDR) VALUES('4', 'SOURCE=4', NULL); INSERT INTO WUDI.USER_SOURCE (ID, NAME, ADDR) VALUES('5', 'SOURCE=5', NULL); 2.2.1、USER_TARGET 表数据如下
2.2.2、USER_SOURCE 表数据如下
2.3、使用merge into 更新
-- MERGE INTO : USER_SOURCE 数据 合并到 USER_TARGET , 存在修改；不存在添加 MERGE INTO USER_TARGET T1 USING USER_SOURCE T2 ON (T1.ID = T2.ID ) WHEN MATCHED THEN UPDATE SET T1.NAME = T2.NAME WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES(T2.ID,T2.NAME) ; 2.3.1、merge into 更新后 USER_TARGET 表数据如下
2.4、使用merge into 更新 --- 子查询
-- 3.2、不存在 ID=7,8 添加数据 MERGE INTO USER_TARGET T1 USING ( SELECT 7 AS ID , 'SOURCE=7' AS NAME FROM DUAL UNION ALL SELECT 8 AS ID , 'SOURCE=8' AS NAME FROM DUAL )T2 ON (T1.ID = T2.ID) WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES(T2.ID,T2.NAME) ; 2.4.1、merge into 更新---子查询 USER_TARGET 表数据如下
3、MyBatis中使用示例
<update id="updateBatch" parameterType="list"> MERGE INTO USER_TARGET T1 USING ( <foreach collection="list" item="e" separator="UNION ALL"> SELECT #{e.id} AS ID , #{e.name} AS NAME FROM DUAL </foreach> ) T2 ON (T1.ID = T2.ID ) WHEN MATCHED THEN UPDATE SET T1.NAME = T2.NAME WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES(T2.ID,T2.NAME) ; </update> 4、merge into NULL值问题
在使用merge into语句时，若遇到 on 中遇到的两个字段值，都是null值的情况，不会执行 when matched then 语句，会一直执行 when not matched then ... ， 也就 on条件中 null=null 的条件是false ， 如本示例中 ，会一直执行 when not matched then 。
ON (T1.ID = T2.ID AND T1.ADDR = T2.ADDR )MERGE INTO USER_TARGET T1 USING ( SELECT * FROM USER_SOURCE a WHERE a.id ='3' )T2 -- ON 条件不满足，会执行insert插入数据 ON (T1.ID = T2.ID AND T1.ADDR= T2.ADDR) WHEN MATCHED THEN UPDATE SET T1.NAME = T2.NAME WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES(T2.ID,T2.NAME) ; 
4.1、COALESCE函数解决 merge into null 值问题
使用 COALESCE 函数，可以将NULL值转换为某个固定的值，来进行判断，这样遇到 NULL=NULL的情况，可以转换为 1=1 ，这样就可以避免NULL值的时候，判断情况不准确的问题。
MERGE INTO USER_TARGET T1 USING ( SELECT * FROM USER_SOURCE a WHERE a.id ='3' )T2 -- COALESCE 函数，将NULL值转换为1 ON (T1.ID = T2.ID AND COALESCE(T1.ADDR,'1') = COALESCE( T2.ADDR,'1')) WHEN MATCHED THEN UPDATE SET T1.NAME = T2.NAME WHEN NOT MATCHED THEN INSERT (ID,NAME) VALUES(T2.ID,T2.NAME) ; 
三、总结
1、on后面的关联条件成立时:
on后面的关联条件成立时: WHEN MATCHED THEN ，可以 update、delete 。
2、on后面的关联条件不成立时:
on后面的关联条件不成立时: WHEN NOT MATCHED THEN ，可以insert
3、只会改变 目标表数据 MERGE INTO TABLE ， 不会改变 源表数据 USING TABLE
4、USING TBALE 支持： 视图 view 、表table 、子查询 subQuery
5、WHEN MATCHED THEN 和 WHEN NOT MATCHED THEN ， 可以二选其一，也可以同时存在。
