1. 首页首页
  2. 编程笔记编程笔记

mergeinto批量更新数据

编程笔记 • • 阅读 15

--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

--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 9999999999999999999999999
        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)就不会了，说明这两个函数还是有区别的。

知秋君
知秋君

知秋君

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请关注公众号“全栈程序员社区”联系小编举报，一经查实，本站将立刻删除。
上一篇 2024-08-29 19:36
下一篇 2024-08-29 19:02

相关推荐