mybatis+oracle常用sql备忘

往数据库中添加一条数据并返回对应的序列主键

selectKey resuitType为返回值类型，keyProperty为返回对象中的属性
to_data()函数，格式化日期格式

<!-- 获取最近一次插入记录的主键值的方式 -->
        <selectKey resultType="java.lang.String" keyProperty="apply_id">
            select SEQ_ASSET_BUY_APPLY.currval from dual
        </selectKey>
        insert into ASSET_BUY_APPLY(
         <include refid="AssetBuy"></include>
        )
        values(
        SEQ_ASSET_BUY_APPLY.NEXTVAL,
        #{clazz,jdbcType=VARCHAR },
        to_date(#{apply_date,jdbcType=DATE},'yyyy-mm-dd'),
        #{name,jdbcType=VARCHAR },
        to_date(#{apply_dep_user_time,jdbcType=DATE},'yyyy-mm-dd hh24:mi:ss')
        )

带序列的批量添加语句

使用foreach标签，collection为入参参数，list对象默认为list作为键，数组对象有"array"代替作为键，map对象没有默认键，如果mapper接口参数给了@param参数则以此为准，item为foreach中list参数的别名，separator 为连接分隔符可以在循环语句后添加达到连接sql语句的功能，open属性为foreach代码的开始符号，close属性为foreach代码的结束的符号

 insert into ASSET_CHECK(
        <include refid="assetCheck"></include>
        )
        select SEQ_ASSET_CHECK.NEXTVAL, A.* from(
        <foreach collection="list" item="assetChecks" separator="union all">
            select
                #{assetChecks.assetName,jdbcType=VARCHAR},
                #{assetChecks.model,jdbcType=VARCHAR},
            to_date(#{assetChecks.dealDate,jdbcType=DATE},'yyyy-mm-dd hh24:mi:ss'),
                #{assetChecks.node,jdbcType=VARCHAR}
            from dual
        </foreach>
        )A

有选择的更新语句

trim标签可以在包含的字段前后添加一些内容，拥有属性：prefix给sql语句拼接的前缀，suffix:给sql语句拼接的后缀,prefixOverrides:去除开头多余的指定代码，suffixOverrides：去除结尾指定的代码

<update id="updateAssetBuyApply">
        update ASSET_BUY_APPLY
        <trim prefix="set" suffixOverrides=",">
            <if test="clazz!=null">CLAZZ = #{clazz,jdbcType=VARCHAR },</if>
            <if test="apply_date!=null">APPLY_DATE = to_date(#	{apply_date,jdbcType=DATE},'yyyy-mm-dd'),</if>
            <if test="name!=null">NAME = #{name,jdbcType=VARCHAR },</if>
        </trim>
        where APPLY_ID=#{apply_id,jdbcType=VARCHAR}
    </update>
    

根据条件批量更新或插入

merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
when matched then --如果上面on条件成里则执行下面的sql
update set a.更新字段=b.字段   --将b数据更新到a表中
 when  not matched the  --如果上面on条件不成立则执行下面的sql
 insert into (a.字段1,a.字段2……)values(b.字段1,b.字段2……) --将b表数据添加到a表中

oracle实用函数

	select to_char(sysdate,'yyyy-MM-dd') from dual;//格式化日期格式
	select to_char(12345678,'999,999,999,999')  from dual;
	select to_char(123,'$99,999.9') from dual; //格式化数字格式
	select to_char(4567,'xxxx') from dual;//十进制转十六进制
	select to_number('7D','XX') from dual;//十六进制转十进制
	SELECT CONCAT('Hello','world') FROM dual;//连接两个字符串
	SELECT LOWER('Hello') FROM dual;//将字符串转换为小写
	SELECT UPPER('hello') FROM dual;//将字符串转换为大写
	SELECT REPLACE('ABCDE','CD','AAA')FROM dual;//将cd替换成aaa
	SELECT SUBSTR('ABCDE',2,3) FROM dual;//从第二个下标开始截取三个长度的字符串
	select CEIL(5.4) from dual;//向上取整
	select FLOOR(5.42) from dual;//向下取整
	select ROUND(3.456，2) from dual;//在小数点后第二位四舍五入 
	select TRUNC(323.456，2) from dual; // 截取323.456 为323.45
	select last_day(SYSDATE)  from dual; //获得指定日期的月份最后一天
	select nvl(name,'张三') from table;	//如果name字段为空返回张三为结果
	select nvl2(x,value1,value2) from table; //如果x字段不为空返回value1为空返回value2
	select LENGTH(rawtohex(sys_guid())) from dual;//生成uuid并处理为标准格式并查看其长度

##恢复某个时间段的数据用于误更新情况下

根据oracle自己的快照备份查询某一时刻的某张表数据

 select * from 表名 as of timestamp to_timestamp('2019-06-08 11:06:00', 'yyyy-MM-dd HH:mi:ss');

可直接删除表数据，再插入历史快照数据：

delete from 表名;

commit;

insert into 表名 select * from 表名 as of timestamp to_timestamp('2019-06-08 11:06:00', 'yyyy-MM-dd HH:mi:ss');

#{}与${}的区别:
#{}是预编译处理，$ {}是字符串替换。#{}是将传入的参数用 ? 号表示然后调动PreparedStatement的set方法来处理，可以防止sql注入，${}直接将变量的值替换为传入的参数，一般#{}在客户端传递参数时使用，${}在写程序时不给其他人传入并且格式不为字符串时使用
union all 与union的区别：
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

序列名.NEXTVAL生成下一个序列