开发中常见 Oracle和Mysql 语句差异

 批量插入语句

Mysql：

INSERT into logininfo(username,password,state,userType)values ('kenho','123456',1,2),('kenho','123456',1,2)

Oracle：

insert all
into logininfo values(1,'kenho','123456',1,2)
into logininfo values(2,'kenho','123456',1,2)
into logininfo values(3,'kenho','123456',1,2)
select 1 from dual

日期格式化

Mysql：

SELECT DATE_FORMAT('2011-09-20 08:30:45', '%Y-%d-%m') 

Oracle：

SELECT to_date('2011-09-20 08:30:45', 'yyyy-MM-dd hh24:mi:ss') FROM dual;

空判断

Mysql：

select ifnull(max(cast(${CoulmnValueName} as unsigned int)),0)  from ${tableName}

Oracle：

select nvl(max(to_number(${CoulmnValueName})),0)  from ${tableName}