oracle新增，批量新增，存在则更新否则插入的SQL写法_oracle新增存在就更新

1.最常用的新增写法

 <insert id="insertUserPasswordLog" parameterType="java.util.Map">
        insert into ADMIN_PASSWORD (LOG_ID, PWD_UPED, USER_ID, LAST_CHG_USR, LAST_CHG_DT)
        values (#{LOG_ID,jdbcType=VARCHAR},             
                #{PWD_UPED,jdbcType=VARCHAR},              
                #{USER_ID,jdbcType=VARCHAR},
                #{LAST_CHG_USR,jdbcType=VARCHAR},
                #{LAST_CHG_DT,jdbcType=VARCHAR})
 </insert>
1
2
3
4
5
6
7
8

2. insert into xxx select 写法

    <insert id="insertUserRoleRel" parameterType="java.util.Map">
        insert into user_role_rel (USER_ROLE_REL_ID, USER_ID, ROLE_ID,LAST_CHG_DT)
        select #{USER_ROLE_REL_ID,jdbcType=VARCHAR},
               #{USER_ID,jdbcType=VARCHAR},
               (select ROLE_ID FROM admin_sm_role WHERE ROLE_CODE = #{ROLE_CODE,jdbcType=VARCHAR}),
               #{LAST_CHG_DT,jdbcType=VARCHAR}
        from dual
    </insert>
1
2
3
4
5
6
7
8

3.批量插入写法(推荐)

   <insert id="insertSiteSpuListLink" parameterType="java.util.List">
        insert into LIST_LINK (ID,LIST_CODE,CREATED_BY,CREATED_ORG_ID,CREATION_TIME,MODIFIED_BY,MODIFIED_ORG_ID,MODIFICATION_TIME,DELETE_STATUS)
        <foreach item="item" index="index" collection="list" separator="union all">
            (
            SELECT
            #{item.id,jdbcType=VARCHAR},
            #{item.listCode,jdbcType=VARCHAR},
            #{item.createdBy,jdbcType=VARCHAR},
            #{item.createdOrgId,jdbcType=VARCHAR},
            sysdate,
            #{item.createdBy,jdbcType=VARCHAR},
            #{item.createdOrgId,jdbcType=VARCHAR},
            sysdate,
            '0'
            FROM DUAL
            )
        </foreach>
    </insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4.存在则更新，不存在则插入

    <insert id="insertOrSaveUser" parameterType="cn.com.Test.GroundPersonInfo">
        merge into USER_INFO T
        using (
        select #{id,jdbcType=VARCHAR} ID,
        #{userCode,jdbcType=VARCHAR} USER_CODE,
        #{userName,jdbcType=VARCHAR} USER_NAME,
        #{gender,jdbcType=VARCHAR} USER_SEX,
        #{phone,jdbcType=VARCHAR} USER_MOBILEPHONE,
        #{educationCode,jdbcType=VARCHAR} USER_EDUCATION
        from dual
        ) T1
        on(T.user_id = T1.id)
        when matched then
        update set
        T.USER_NAME = T1.USER_NAME,T.USER_SEX=T1.USER_SEX,T.USER_CODE=T1.USER_CODE,T.USER_MOBILEPHONE=T1.USER_MOBILEPHONE,T.USER_EDUCATION=T1.USER_EDUCATION
        when not matched then
        insert(  USER_ID,USER_NAME,USER_CODE,USER_SEX,USER_MOBILEPHONE,USER_EDUCATION
        )values (
        T1.id,T1.USER_NAME,T1.USER_CODE,T1.USER_SEX,T1.USER_MOBILEPHONE,T1.USER_EDUCATION
        )
    </insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21