日期:2014-05-16  浏览次数:20901 次

oracle移植至mysql相关sql语句

?

一. 项目已用到 oracle 函数的转换

1.? Oracle 中的 TO_DATE ()

示例: select * from admadjustmoney t where t.sendtime> to_date(?,'yyyy-mm-dd????? hh24:mi:ss') and t.sendtime< to_date(?,'yyyy-mm-dd hh24:mi:ss')

转换后:

SELECT *

FROM `ADMADJUSTMONEY` t

WHERE t.SENDTIME > STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') AND

????? t.SENDTIME < STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') ? 注意表名字段名的大小写 MYSQL? 大小写敏感 ?? sql 统一大写 hql 统一小写

2.? Oracle 中的 nvl ()

示例 : select distinct a.id,a.parentId,a.description,a.name,a.status, NVL(ur.roleid,'') AS roleid from admrole a left join admuserrole ur on (a.id = ur.roleid and ur.userid = ? and ur.status = 1) where a.status = ?

转换后:

select distinct a.id,a.parentId,a.description,a.name,a.status, IFNULL(ur.roleid,'') AS roleid from admrole a left join admuserrole ur on (a.id = ur.roleid and ur.userid = ? and ur.status = 1) where a.status = ?

3.? Oracle 中的 decode ()

示例:

SELECT DECODE(MAX(PIECECODE), NULL, 0, MAX(PIECECODE)) AS PIECECODE FROM PUBPAGEPIECE WHERE 1=1

转换后:

SELECT if(MAX(PIECECODE) IS NULL, 0, MAX(PIECECODE)) AS PIECECODE FROM PUBPAGEPIECE WHERE 1=1

或者用