oracle与mysql差别

项目开发中 mysql转型oracle遇到的兼容问题

一、查询

mysql

select * from sys_role where id in 
  (select a.id from(SELECT id FROM sys_role where create_organ_id  IN ('1')
  
       or create_depart_id IN('1')
    
       )a)

如果不定义别名，则会报错

oracle 只需

select * from sys_role where id in 
  (select  from(SELECT id FROM sys_role where create_organ_id  IN ('1')
  
       or create_depart_id IN('1') 
       ))

一、除法

mysql

UPDATE sys_organ
SET grade = (LENGTH (
CONCAT (1110144, SUBSTR(code, LENGTH(1110144) + 1))
) div 3),
 code = CONCAT (1110144, SUBSTR(code, LENGTH(1110144) + 1))
WHERE
code LIKE 1110144|| '%'

在mysql中是可以的 而在oracle中则需写成"/"

UPDATE sys_organ
SET grade = (LENGTH (
CONCAT (1110144, SUBSTR(code, LENGTH(1110144) + 1))
) / 3),
 code = CONCAT (1110144, SUBSTR(code, LENGTH(1110144) + 1))
WHERE
code LIKE 1110144|| '%'