Oracle汇总

1.批量操作

1.1.批量添加

<insert id="batchInsert">
insert into USER
(
	ID,NAME,AGE,CREATETIME
)
select SEQ_ID.NEXTVAL,a.*
from (
<foreach collection="users" item="user" separator="union all">
	select
		#{user.name,jdbcType=VARCHAR},
		#{user.age,jdbcType=INTEGER},
		sysdate
	from dual
</foreach>
) a
</insert>

补充知识：
MyBatis会将参数自动转换成数据库对应的类型，但当传入的参数为null时，类型转换出错，此时我们需要手动指定转换类型用到jdbcType，通常我们不会去判断某个参数是否为空，因此所有参数都手动添加指定类型。

1.2.批量删除

<delete id="batchDel">
delete from USER
where (id1,id2) in (
	select id1,id2 from
	(
	<foreach collect="users" item="user" separator="union all">
	select
		#{user.id1,jdbcType=VARCHAR},
		#{user.id2,jdbcType=VARCHAR}
	from dual
	</foreach>
	)
)
</delete>

2.特殊需求

2.1.横向转纵向

<select id="getExceptionScale" resultType="...">
    select EXTRACTVALUE(VALUE(X), '/row') name, count(1) value
    from (select '&lt;rows>' ||
    regexp_replace('&lt;row>' || replace(k.bked13, ';', '&lt;/row>&lt;row>'),
        <![CDATA['</row><row>$',]]>
        '&lt;/row>') || '&lt;/rows>' as data
    from kes5 k
    LEFT JOIN medical_js mj
    on k.BAZ506 = mj.BAZ506 and k.BKE139 = mj.BKE139
    where k.bked13 is not null and mj.AAA131 = '0'
    <if test="kes5DTO.akb020 != null and kes5DTO.akb020 != ''">
        and k.AKB020 = #{kes5DTO.akb020,jdbcType=VARCHAR}
    </if>
    <if test="kes5DTO.aae036 != null and kes5DTO.aae036 != ''">
        and substr(to_char(mj.AAE036, 'yyyy-mm-dd HH24:mi:ss'),0,7) = #{kes5DTO.aae036,jdbcType=VARCHAR}
    </if>
    ) t,
    XMLTABLE('/rows/row' PASSING xmltype(t.data)) x
    group by EXTRACTVALUE(VALUE(X), '/row')
    order by value desc
</select>

2.2.纵向转横向（网上例子）

NO name KM CJ
001 张三 语文 80
001 张三 数学 86
001 张三 英语 75
002 李四 语文 78
002 李四 数学 85
002 李四 英语 78

select decode(grouping(name),1,'总分',name) 姓名, 
	sum(decode(km, '数学',cj,0)) 数学,
	sum(decode(km, '英语',cj,0)) 英语, 
	sum(decode(km, '语文',cj,0)) 语文,
	sum(cj) 总成绩 
from test group by rollup(name)

纵转横结果：
姓名 数学 英语 语文 总成绩
李四 85 78 78 241
张三 86 75 80 241
总分 171 153 158 482

3.不同表之间数据比对

需求：查找 用户表user和雇员表employee中 既是客户又是雇员的人员信息

select * 
from user u
where EXISTS
(
	SELECT 1
	from employee e
	where
		u.user_id = e.employee_id
		and u.user_name = e.employee_name
		and u.age = e.age
)

++++++++++++++++++++++++++++++++
未完待续
++++++++++++++++++++++++++++++++