mybatis中mysql和oracle的区别

   mysql和oracle语法有一定的差异，我们将服务由部署mysql的服务器迁移到部署oracle的服务器上时，需要修改sql语句。下面说说mybatis中由mysql转为oracle需要修改的语句。

1.批量插入

mysql：

<insert id="insertBatch" parameterType="List">  
        insert into black_list  
        (uuid,type,value,delete_flag,gmt_create,gmt_modified) values  
        <foreach collection="list" item="item" index="index"  
            separator=",">  
            (#{item.uuid},#{item.type},#{item.value}  
            #{item.deleteFlag},#{item.gmtCreate},#{item.gmtModified})  
        </foreach>  
    </insert> 

oracle：

 <insert id="insertBatch">  
        <selectKey keyProperty="id" resultType="Long" order="BEFORE">  
            select seq_black_list.nextval as id from dual  
        </selectKey>  
        insert into black_list  
        (id,uuid,type,value,delete_flag,gmt_create,gmt_modified)  
        select seq_black_list.nextval, A.* FROM (  
        <foreach collection="list" item="item" index="index"  
            separator="union all">  
            select  
            #{item.uuid,jdbcType=VARCHAR},  
            #{item.type,jdbcType=VARCHAR},  
            #{item.value,jdbcType=VARCHAR},  
            #{item.deleteFlag,jdbcType=INTEGER},  
            #{item.gmtCreate,jdbcType=DATE},  
            #{item.gmtModified,jdbcType=DATE}  
            from  
            dual  
        </foreach>  
        ) A  
    </insert> 

其中，oracle在执行foreach之前，必须使用select语句。

2.批量更新

mysql：

<update id="batchUpdate"  parameterType="java.util.List">
	 
	      <foreach collection="list" item="item" index="index" open="" close="" separator=";">
				update test 
				<set>
				  test=${item.test}+1
				</set>
				where id = ${item.id}
		 </foreach>
		 
    </update>

oracle：

<update id="batchUpdate"  parameterType="java.util.List">
	 
	   <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">
				update test 
				<set>
				  test=${item.test}+1
				</set>
				where id = ${item.id}
	   </foreach>
		 
    </update>

其中，oracle的foreach语句需要用begin和end包起来。

3.limit

mysql：

SELECT * FROM tablename LIMIT 100,15

oracle：

 select * from 
     (select A.*,rownum rn from
            (   
                   原mysql的语句，注意去掉limit
             )A 
            where rownum > 100
      )
      where rn <= 115

由于oracle中没有limit，因此可以通过rownum来实现相同的效果。

4.自增主键

mysql：

<insert id="insert" parameterType="Role" useGeneratedKeys="true" keyProperty="roleId">
    insert into role (name, create_time, update_time) values (#{name,jdbcType=VARCHAR}, now(), now())
</insert>

oracle：

在执行插入之前，需要创建一个序列：

create sequence SEQ_T_USER_ID
       minvalue 1
       maxvalue 9999999999999999999999999999
       start with 1
       increment by 1
       cache 20;

序列生成后，就可以始终该序列实现主键自增的效果：

<insert id="insert" parameterClass="ROLE">
<selectKey resultType="java.lang.Long" order="AFTER" keyProperty="roleId"> 
       SELECT SEQ_T_USER_ID.currval as ROLEID from DUAL 

  </selectKey>
 insert into ROLE(ID, NAME, CREATE, MODIFY) values (#{id}, #{name}, sysdate, sysdate)
</insert>

mysql可以通过设置useGeneratedKeys为true来实现主键自增，oracle的语法不支持。为解决mysql和oracle的主键自增问题，可以用另外一张表TEMP记录其他表的主键，在插入时，由TEMP获取相应表的主键值，将其插入。

5.时间操作

mysql：

返回系统日期,输出 2009-12-25 14:38:59
select now();
输出 09-12-25
select date_format(now(),'%y-%m-%d');

获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()

当前时间减去7分钟的时间 :select date_sub(now(),interval 7 minute);

获取时间戳：select unix_timestamp('2008-08-08'); -- 1218124800

oracle：

获取系统日期：  SYSDATE()
格式化日期：     TO_CHAR(SYSDATE(),'YY/MM/DD HH24:MI:SS')
返回系统日期,输出 25-12月-09
select sysdate from dual;
mi是分钟，输出 2009-12-25 14:23:31
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
当前时间减去7分钟的时间 
select sysdate,sysdate - interval '7' MINUTE from dual; 
获取时间戳：SELECT (SYSDATE - TO_DATE('1970-1-1 8','YYY-MM-DD HH24'))*86400 FROM DUAL;
用当前的时间减去1970年1月1日8时，得到的天数乘以24小时乘以3600秒，得到的结果就是系统时间戳。
这里用8时的原因时系统所处时区为东8区

含义解释： 
Dual伪列
      Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select语句块中。
      不同系统可能返回日期的格式不一样。
      返回当前连接的用户：select user from dual;