--2020.03.26：oracle没有NOT MATCHED BY SOURCE 语法，但是可以在match的条件里删除，

MERGE INTO AIMS_ACCOUNT_ATTR T USING (SELECT AA.ACCOUNTID ACCOUNTID, GCD42.V_45 ACCOUNTNO, 1 IS_LAW_PERMEATION FROM GDT_CUST_GDTCLMS42 GCD42 JOIN AIMS_ACCOUNT AA ON AA.ACCOUNTNO = GCD42.V_45) GDT ON (GDT.ACCOUNTID = T.ACCOUNTID) WHEN MATCHED THEN UPDATE SET T.IS_LAW_PERMEATION = GDT.IS_LAW_PERMEATION DELETE WHERE GDT.ACCOUNTID = T.ACCOUNTID WHEN NOT MATCHED THEN INSERT (ID, ACCOUNTID, ACCOUNTNO, IS_LAW_PERMEATION) VALUES (AIMS_ACCOUNT_ATTR_SEQ.NEXTVAL, GDT.ACCOUNTID, GDT.ACCOUNTNO, 1) 

这种局限性也比较大，可以考虑在using的表上处理

看一个案例：

我有一个账户表 aims_account ,融资信息子表 GDT_CUST_GDTCLMS42 ，账户信息子表

现在要把GDT_CUST_GDTCLMS42 里面的帐号同步到账户子表aims_account_attr，

因为aims_account_attr还有其他信息所以尽量不能删除，如果GDT_CUST_GDTCLMS42里面的账户被删除了，就把aims_account_attr表的一个字段的值改为0

讯享网MERGE INTO AIMS_ACCOUNT_ATTR T USING ( SELECT AAA.ACCOUNTID ACCOUNTID,AAA.ACCOUNTNO ACCOUNTNO,0 IS_LAW_PERMEATION FROM AIMS_ACCOUNT_ATTR AAA WHERE NOT EXISTS (SELECT 1 FROM GDT_CUST_GDTCLMS42 GDT42 WHERE GDT42.V_45 = AAA.ACCOUNTNO) UNION SELECT AA.ACCOUNTID ACCOUNTID, AA.ACCOUNTNO ACCOUNTNO, 1 IS_LAW_PERMEATION FROM GDT_CUST_GDTCLMS42 GDT42 JOIN AIMS_ACCOUNT AA ON AA.ACCOUNTNO = GDT42.V_45 ) GDT ON (GDT.ACCOUNTID = T.ACCOUNTID) WHEN MATCHED THEN UPDATE SET T.IS_LAW_PERMEATION = GDT.IS_LAW_PERMEATION WHEN NOT MATCHED THEN INSERT (ID, ACCOUNTID, ACCOUNTNO, IS_LAW_PERMEATION) VALUES (AIMS_ACCOUNT_ATTR_SEQ.NEXTVAL, GDT.ACCOUNTID, GDT.ACCOUNTNO, GDT.IS_LAW_PERMEATION)

使用环境IBATIS + ORACLE

页面：

讯享网

建表语句：

--序列 DECLARE CNT INTEGER; BEGIN SELECT COUNT(0) INTO CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER('SEQ_AIMS_FILE_CONTROL'); IF CNT = 0 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_AIMS_FILE_CONTROL MINVALUE 1 MAXVALUE 999 START WITH 1 INCREMENT BY 1 CACHE 20'; END IF; END; / --表 DECLARE CNT INTEGER; BEGIN SELECT COUNT(0) INTO CNT FROM USER_ALL_TABLES WHERE TABLE_NAME = UPPER('AIMS_FILE_CONTROL'); IF CNT = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE AIMS_FILE_CONTROL( CONTROL_ID NUMBER, ATTNO VARCHAR2(32), CREATE_PERSON VARCHAR2(32), CREATE_TIME DATE, UPDATE_PERSON VARCHAR2(32), UPDATE_TIME DATE, ACCOUNT_APPLY VARCHAR2(32), ACCOUNT_REGIST VARCHAR2(32), ACCOUNT_CHANGE_APPLY VARCHAR2(32), ACCOUNT_CHANGE_REGIST VARCHAR2(32), ACCOUNT_REVISE VARCHAR2(32), ACCOUNT_CANCEL_APPLY VARCHAR2(32), ACCOUNT_CANCEL_REGIST VARCHAR2(32), EBANK_APPLY VARCHAR2(32), EBANK_REGIST VARCHAR2(32), EBANK_CHANGE_APPLY VARCHAR2(32), EBANK_CHANGE_REGIST VARCHAR2(32), EBANK_REVISE VARCHAR2(32), EBANK_CANCEL_APPLY VARCHAR2(32), EBANK_CANCEL_REGIST VARCHAR2(32) )'; END IF; END; / COMMENT ON TABLE AIMS_FILE_CONTROL IS '集团账户附件控制' / COMMENT ON COLUMN AIMS_FILE_CONTROL.CONTROL_ID IS '主键' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ATTNO IS '附件类型编号' / COMMENT ON COLUMN AIMS_FILE_CONTROL.CREATE_PERSON IS '创建者' / COMMENT ON COLUMN AIMS_FILE_CONTROL.CREATE_TIME IS '创建时间' / COMMENT ON COLUMN AIMS_FILE_CONTROL.UPDATE_PERSON IS '修改人' / COMMENT ON COLUMN AIMS_FILE_CONTROL.UPDATE_TIME IS '修改时间' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_APPLY IS '账户申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_REGIST IS '账户登记' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_CHANGE_APPLY IS '账户变更申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_CHANGE_REGIST IS '账户变更登记' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_REVISE IS '账户修正' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_CANCEL_APPLY IS '账户销户申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.ACCOUNT_CANCEL_REGIST IS '账户销户登记' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_APPLY IS '网银申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_REGIST IS '网银登记' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_CHANGE_APPLY IS '网银变更申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_CHANGE_REGIST IS '网银变更登记' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_REVISE IS '网银修正' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_CANCEL_APPLY IS '网银销户申请' / COMMENT ON COLUMN AIMS_FILE_CONTROL.EBANK_CANCEL_REGIST IS '网银销户登记' / DECLARE CNT INTEGER; BEGIN SELECT COUNT(0) INTO CNT FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = UPPER('AIMS_FILE_CONTROL_PK'); IF CNT = 0 THEN EXECUTE IMMEDIATE 'ALTER TABLE AIMS_FILE_CONTROL ADD CONSTRAINT AIMS_FILE_CONTROL_PK PRIMARY KEY(CONTROL_ID)'; END IF; END; / 

原来的写法：新增和修改分开，并且不能执行批量操作：

新增：

讯享网 <!-- 新增集团账户附件控制 --> <insert id="saveFileControl" parameterClass="com.nstc.aims.model.FileControl"> <selectKey resultClass="java.lang.Integer" keyProperty="controlId"> SELECT SEQ_AIMS_FILE_CONTROL.NEXTVAL AS controlId FROM DUAL </selectKey> INSERT INTO AIMS_FILE_CONTROL ( CONTROL_ID, ATTNO, CREATE_PERSON, CREATE_TIME, UPDATE_PERSON, UPDATE_TIME, ACCOUNT_APPLY, ACCOUNT_REGIST, ACCOUNT_CHANGE_APPLY, ACCOUNT_CHANGE_REGIST, ACCOUNT_REVISE, ACCOUNT_CANCEL_APPLY, ACCOUNT_CANCEL_REGIST, EBANK_APPLY, EBANK_REGIST, EBANK_CHANGE_APPLY, EBANK_CHANGE_REGIST, EBANK_REVISE, EBANK_CANCEL_APPLY, EBANK_CANCEL_REGIST ) VALUES( #controlId#, #attno#, #createPerson#, #createTime#, #updatePerson#, #updateTime#, #accountApply#, #accountRegist#, #accountChangeApply#, #accountChangeRegist#, #accountRevise#, #accountCancelApply#, #accountCancelRegist#, #ebankApply#, #ebankRegist#, #ebankChangeApply#, #ebankChangeRegist#, #ebankRevise#, #ebankCancelApply#, #ebankCancelRegist# ) </insert>

修改：

 <!-- 修改集团账户附件控制 --> <update id="updateFileControl" parameterClass="com.nstc.aims.model.FileControl"> UPDATE AIMS_FILE_CONTROL T SET T.CONTROL_ID = #controlId# <isNotNull prepend="," property="attno"> T.ATTNO = #attno# </isNotNull> <isNotNull prepend="," property="createPerson"> T.CREATE_PERSON = #createPerson# </isNotNull> <isNotNull prepend="," property="createTime"> T.CREATE_TIME = #createTime# </isNotNull> <isNotNull prepend="," property="updatePerson"> T.UPDATE_PERSON = #updatePerson# </isNotNull> <isNotNull prepend="," property="updateTime"> T.UPDATE_TIME = #updateTime# </isNotNull> <isNotNull prepend="," property="accountApply"> T.ACCOUNT_APPLY = #accountApply# </isNotNull> <isNotNull prepend="," property="accountRegist"> T.ACCOUNT_REGIST = #accountRegist# </isNotNull> <isNotNull prepend="," property="accountChangeApply"> T.ACCOUNT_CHANGE_APPLY = #accountChangeApply# </isNotNull> <isNotNull prepend="," property="accountChangeRegist"> T.ACCOUNT_CHANGE_REGIST = #accountChangeRegist# </isNotNull> <isNotNull prepend="," property="accountRevise"> T.ACCOUNT_REVISE = #accountRevise# </isNotNull> <isNotNull prepend="," property="accountCancelApply"> T.ACCOUNT_CANCEL_APPLY = #accountCancelApply# </isNotNull> <isNotNull prepend="," property="accountCancelRegist"> T.ACCOUNT_CANCEL_REGIST = #accountCancelRegist# </isNotNull> <isNotNull prepend="," property="ebankApply"> T.EBANK_APPLY = #ebankApply# </isNotNull> <isNotNull prepend="," property="ebankRegist"> T.EBANK_REGIST = #ebankRegist# </isNotNull> <isNotNull prepend="," property="ebankChangeApply"> T.EBANK_CHANGE_APPLY = #ebankChangeApply# </isNotNull> <isNotNull prepend="," property="ebankChangeRegist"> T.EBANK_CHANGE_REGIST = #ebankChangeRegist# </isNotNull> <isNotNull prepend="," property="ebankRevise"> T.EBANK_REVISE = #ebankRevise# </isNotNull> <isNotNull prepend="," property="ebankCancelApply"> T.EBANK_CANCEL_APPLY = #ebankCancelApply# </isNotNull> <isNotNull prepend="," property="ebankCancelRegist"> T.EBANK_CANCEL_REGIST = #ebankCancelRegist# </isNotNull> WHERE CONTROL_ID = #controlId# </update>

如果修改或者新增的数据只有一条，那么可以使用这种方式新增（using dual）

讯享网 <!-- 设置融资附件上传控制 --> <insert id="saveFileUploadControl" parameterClass="java.util.HashMap"> merge into GDT_FILE_CONTROL c using dual on (c.attno = #fileType#) when matched then update set c.APPLY = #applyCheck#, c.CONTRACT = #conCheck#, c.CHANGE = #changeCheck#, c.DRAW = #drawCheck#, c.BOND = #bondCheck#, c.REPAIR = #repairCheck#, c.UPDATEONE = #updateOne#, c.UPDATETIMA = sysdate where c.attno = #fileType# when not matched then insert (attno, APPLY, CONTRACT, CHANGE, DRAW, BOND, REPAIR, CREATOR, CREATTIMA, UPDATEONE, UPDATETIMA) values(#fileType#, #applyCheck#, #conCheck#, #changeCheck#, #drawCheck#, #bondCheck#, #repairCheck#, #creator#, sysdate, #updateOne#, sysdate) </insert>

我改进的方法：批量新增或修改

 <!-- 保存或修改集团账户附件控制集合 --> <update id="saveOrUpdateFileControlList" parameterClass="java.util.ArrayList"> MERGE INTO AIMS_FILE_CONTROL T USING <iterate open="(" close=") A" conjunction="UNION"> SELECT <!-- 主键为空时候返回字符串，与原类型不匹配，非字符串类型都需要做转换，这里不要使用NVL --> DECODE(#list[].controlId#,NULL,NULL,#list[].controlId#) AS CONTROL_ID, #list[].attno# AS ATTNO, #list[].createPerson# AS CREATE_PERSON, NVL(#list[].createTime#,NULL) AS CREATE_TIME, #list[].updatePerson# AS UPDATE_PERSON, NVL(#list[].updateTime#,NULL) AS UPDATE_TIME, #list[].accountApply# AS ACCOUNT_APPLY, #list[].accountRegist# AS ACCOUNT_REGIST, #list[].accountChangeApply# AS ACCOUNT_CHANGE_APPLY, #list[].accountChangeRegist# AS ACCOUNT_CHANGE_REGIST, #list[].accountRevise# AS ACCOUNT_REVISE, #list[].accountCancelApply# AS ACCOUNT_CANCEL_APPLY, #list[].accountCancelRegist# AS ACCOUNT_CANCEL_REGIST, #list[].ebankApply# AS EBANK_APPLY, #list[].ebankRegist# AS EBANK_REGIST, #list[].ebankChangeApply# AS EBANK_CHANGE_APPLY, #list[].ebankChangeRegist# AS EBANK_CHANGE_REGIST, #list[].ebankRevise# AS EBANK_REVISE, #list[].ebankCancelApply# AS EBANK_CANCEL_APPLY, #list[].ebankCancelRegist# AS EBANK_CANCEL_REGIST FROM DUAL </iterate> ON (A.CONTROL_ID = T.CONTROL_ID) WHEN MATCHED THEN UPDATE SET T.ATTNO = A.ATTNO, T.UPDATE_PERSON = A.UPDATE_PERSON, T.UPDATE_TIME = SYSDATE, T.ACCOUNT_APPLY = A.ACCOUNT_APPLY, T.ACCOUNT_REGIST = A.ACCOUNT_REGIST, T.ACCOUNT_CHANGE_APPLY = A.ACCOUNT_CHANGE_APPLY, T.ACCOUNT_CHANGE_REGIST = A.ACCOUNT_CHANGE_REGIST, T.ACCOUNT_REVISE = A.ACCOUNT_REVISE, T.ACCOUNT_CANCEL_APPLY = A.ACCOUNT_CANCEL_APPLY, T.ACCOUNT_CANCEL_REGIST = A.ACCOUNT_CANCEL_REGIST, T.EBANK_APPLY = A.EBANK_APPLY, T.EBANK_REGIST = A.EBANK_REGIST, T.EBANK_CHANGE_APPLY = A.EBANK_CHANGE_APPLY, T.EBANK_CHANGE_REGIST = A.EBANK_CHANGE_REGIST, T.EBANK_REVISE = A.EBANK_REVISE, T.EBANK_CANCEL_APPLY = A.EBANK_CANCEL_APPLY, T.EBANK_CANCEL_REGIST = A.EBANK_CANCEL_REGIST WHEN NOT MATCHED THEN INSERT ( CONTROL_ID, ATTNO, CREATE_PERSON, CREATE_TIME, UPDATE_PERSON, UPDATE_TIME, ACCOUNT_APPLY, ACCOUNT_REGIST, ACCOUNT_CHANGE_APPLY, ACCOUNT_CHANGE_REGIST, ACCOUNT_REVISE, ACCOUNT_CANCEL_APPLY, ACCOUNT_CANCEL_REGIST, EBANK_APPLY, EBANK_REGIST, EBANK_CHANGE_APPLY, EBANK_CHANGE_REGIST, EBANK_REVISE, EBANK_CANCEL_APPLY, EBANK_CANCEL_REGIST ) VALUES ( SEQ_AIMS_FILE_CONTROL.NEXTVAL, A.ATTNO, A.CREATE_PERSON, SYSDATE, A.UPDATE_PERSON, SYSDATE, A.ACCOUNT_APPLY, A.ACCOUNT_REGIST, A.ACCOUNT_CHANGE_APPLY, A.ACCOUNT_CHANGE_REGIST, A.ACCOUNT_REVISE, A.ACCOUNT_CANCEL_APPLY, A.ACCOUNT_CANCEL_REGIST, A.EBANK_APPLY, A.EBANK_REGIST, A.EBANK_CHANGE_APPLY, A.EBANK_CHANGE_REGIST, A.EBANK_REVISE, A.EBANK_CANCEL_APPLY, A.EBANK_CANCEL_REGIST ) </update>

需要注意：

1.用java获得的list类型的对象，null被解析成‘’（空字符串），使用union的时候，如果字段类型不是字符串则可能会提示：ORA-01790: 表达式必须具有与对应表达式相同的数据类型。

2.主键是NUMBER类型，如果使用NVL（xxx，NULL），新增的时候会报错ORA-01790: 表达式必须具有与对应表达式相同的数据类型。使用DECODE(XXX,NULL,NULL,XXX)就不会了，说明这两个函数还是有区别的。