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

最新推荐文章于 2024-07-17 19:00:48 发布

XJC_jin

最新推荐文章于 2024-07-17 19:00:48 发布

阅读量3.2k

点赞数 1

分类专栏：工作文章标签： java sql oracle

本文链接：https://blog.csdn.net/XJC_jin/article/details/123984135

版权

工作专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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>

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>

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>

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>