Oracle一些通用Sql[mybatis]，日常整理笔记

Oracle一些通用Sql[mybatis]，日常整理笔记

数据通用插入

• merge into相当于mysql中的replace into，表达起来oracle要复杂一些。
• 作用是，如果目标表中已存在要插入数据的主键数据时，则更新该数据；否则插入一条新数据

<!-- 根据map插入数据 -->
<insert id="InsertByMap" parameterType="java.lang.String">
    INSERT INTO ${tablename}
    <foreach collection="map.entrySet()" index="key" separator="," open="(" close=")">
        ${key}
    </foreach>
    VALUES
    <foreach collection="map.entrySet()" item="val" separator="," open="(" close=")">
        #{val}
    </foreach>
</insert>

<!-- 根据list插入数据[keyList - 字段名; valList - 字段对应值] -->
<insert id="InsertByList" parameterType="java.lang.String">
    INSERT INTO ${tablename}
    <foreach collection="keyList" item="key" separator="," open="(" close=")">
        ${key}
    </foreach>
    VALUES
    <foreach collection="valList" item="val" separator="," open="(" close=")">
        #{val}
    </foreach>
</insert>

<!-- 根据keymap,map插入或更新数据[keymap为主键字段，map为剩余字段] -->
<update id="updateAndInsertByMap" parameterType="java.lang.String">
    MERGE INTO ${tablename} T
    USING (SELECT
    <foreach collection="keymap.entrySet()" index="key" item="val" separator=",">
        &apos;${val}&apos; ${key}
    </foreach>
    FROM DUAL) S ON ( 1=1
    <foreach collection="keymap.entrySet()" index="key">
        AND T.${key} = S.${key}
    </foreach>
    )
    when matched then
    update set
    <foreach collection="map.entrySet()" index="key" item="val" separator=",">
        T.${key} = #{val}
    </foreach>
    when not matched then
    insert
    <foreach collection="keymap.entrySet()" index="key" separator="," open="(" close=",">
        ${key}
    </foreach>
    <foreach collection="map.entrySet()" index="key" separator="," close=")">
        ${key}
    </foreach>
    values
    <foreach collection="keymap.entrySet()" item="val" separator="," open="(" close=",">
        #{val}
    </foreach>
    <foreach collection="map.entrySet()" item="val" separator="," close=")">
        #{val}
    </foreach>
</update>

清空表(比delete要快)

<!-- 根据表名清空表 -->
<delete id="truncateTable" parameterType="java.lang.String">
    truncate table ${tablename}
</delete>

根据条件，查询指定表的某些字段(全部字段，主键，日期字段)

<!-- 根据表名，获取字段名 -->
<select id="queryFieldsByTablename" parameterType="java.lang.String" resultType="java.lang.String">
     SELECT COLUMN_NAME FROM COLS WHERE TABLE_NAME = upper(#{tablename}) order by column_id
</select>

<!--  根据表名，获取主键字段名 -->
<select id="queryKeyFieldsByTablename" parameterType="java.lang.String" resultType="java.lang.String">
      select col.column_name
      from user_constraints con, user_cons_columns col
      where con.constraint_name = col.constraint_name
        and con.constraint_type='P'
        and col.table_name = upper(#{tablename})
</select>

<!-- 根据表名，获取日期字段名 -->
<select id="queryDateFieldsByTablename" parameterType="java.lang.String" resultType="java.lang.String">
    SELECT COLUMN_NAME FROM COLS WHERE TABLE_NAME = upper(#{tablename}) AND DATA_TYPE='DATE'
</select>

一条sql，直接将某张表直接备份至另一张表

• hi表比被备份表多一个备份批次号字段。

<!-- 根据现有表，备份Hi表 -->
<update id="bkDataFromTable" parameterType="java.lang.String">
    INSERT INTO ${writetable} select #{batchno} ,a.* from ${readtable} a
</update>