oracle转MySQL时基本语法的区别

注释

–Oracle注释可以–后直接写注释内容
– MySQL注释在–后必须有一个空格，否则注释不生效

日期格式化

--oracle                     
select to_char(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;
select to_date(sysdate,'yyyy-mm-dd hh24-mi-ss') from dual;
-- MySQL                     
select date_format(sysdate(),'%Y-%m-%d %H:%i:%S');
select time_format(sysdate(),'%H-%i-%S');
select str_to_date(sysdate(),'%Y-%m-%d %H-%i-%S');

日期函数

--oracle                      
select to_char(add_months(to_date ('20000101','yyyymmdd'),1),'yyyy-mm-dd') from dual; 
-- MySQL                      
select date_add('2000-01-01',interval 1 month); 

字符串截取

--oracle                      
select substr('abcdefg',1,5) from dual; 
-- MySQL                      
select substring('abcdefg',2,3); 
select mid('abcdefg',2,3);    
select substring('abcdefg',2); 
select substring('abcdefg' from 2); 

别名

-- mysql所有子查询必须有别名，Oracle不需要  
Every derived table must have its own alias 
每个派生表都必须有自己的别名  

字符串拼接

--oracle                      
select 'hello' || ' ' || 'world' from dual;
-- mysql                      
select concat('hello',' ','world');

数据转换

--oracle                      
select decode('MatchingStatus','','未匹配''MatchingStatus') from dual; 
-- MySQL                      
select (case when 'MatchingStatus'='' then '未匹配' else 'MatchingStatus' end); 

字符串转数字

--oracle                  
select to_number()        
-- MySQL                  
-- 1. 直接用加法             
select * from orders order by (mark+0) desc
-- 2. 使用函数            
select ('',as type);      
select convert('',type);  
-- type类型：             
-- 浮点数： DECIMAL       
-- 整数：SIGNED           
-- 无符号整数：UNSIGNED   

列转行拼接

--oracle
select listagg(columnName,',') within group(orderby orderName asc) from tableName;
--oracle                      
 ,该函数已被Oracle弃用，不建议使用
select wm_concat(columnName) from tableName;
-- MySQL                      
select group_concat(columnName order by orderColumnName asc separator ',') from tableName;

根据id,查询包含该id的所有值

--oracle
select menuid from menu a start with a.menuid=''  connect by prior upmenuid=menuid;
-- MySQL                      
select menuid from menu a where a.menuid='' union
select menuid from menu b where b.upmenuid='';

字段为空时设置默认值

--oracle 如果param1为空则返回param2,不为空则返回param1 
select nvl(param1,param2) from dual; 
-- MySQL 如果expr1不是null则返回expr1,否则返回expr2
select ifnull(expr1,expr2);

更新或者插入

--oracle                      
MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID) 
 WHEN MATCHED THEN
UPDATE SET A.YEAR=C.YEAR WHEN NOT MATCHED THEN
INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
-- MySQL                      
-- MySQL没有Oracle的merge into 语法，但是有一个on duplicate key update语法（不是标准语法 ），可以类似实现merge into的功能
insert into A_MERGE(id,name,phone) select * from B_MERGE on duplicate key update id=values(id);
-- 注意：id字段是主键或者unique索引，不然只会插入所有行  
-- 更新部分字段时，插入的记录中其他字段就为空了 

MySQL与Oracle insert、update别名的区别

--oracle                      
    --insert:                     
    insert into tableName as alias(alias.clumnName1,alias.clumnName)values('','');
    --update
    update tableName as alias set alias.clumnName='' where alias.clumnName='';

-- MySQL中insert中不允许使用别名 
    -- update                     
    update tableName as alias set alias.clumnName='' where alias.clumnName='';
    -- delete                     
    delete alias from tableName where alias.clumnName=''; 

条件语句

--oracle
select decode(条件,值1,返回值,值2,返回值,...) from dual;
-- MySQL没有类似的函数，只能使用case when
select case when 条件=值1 then 返回值 when 条件=值2 then 返回值2 else 返回值3 end;
```  
15. if判断
```sql
--oracle                      
if 表达式 then                
...                           
elsif 表达式 then             
...                           
else                          
...                           
end if;                       
                                   
-- MySQL既可以作为表达式用，也可在存储过程中作为流程控制语句使用, 
-- 存储过程                   
if 表达式 then                
...                           
elseif 表达式 then            
...                           
else                          
...                           
end if                        
-- 表达式，如果表达式1为true，返回值1，否则返回值2
select if(表达式1，值1，值2)

存储过程调用

-- oracle中存储过程调用参数中间可以有换行符
call procedure(param1,param2,param3);
-- MySQL存储过程调用参数中不能有空格和换行符  
{call procedure(param1,param2,param3)}

取第一行

--oracle
select * from tableName as tab where ROWNUM=1;
-- MySQL                      
select * from tableName limit 1;
select t.*,@rownum:=@rownum+1 as rownum from tableName t,(select @rownum:=0) r where @rownum=1;

分组排序等

--oracle                      
     --排序并添加序号
select column1,column2,row_number() over(order by column1) rs from tableName;
--分组并排序
select column1,column2,row_number over(partition by column2 order by column1);
-- MySQL                      
-- 排序并添加序号             
select a.*,@rownum:=@rownum+1 as rownum from
    (select * from tableName order by clumnName desc) a,(select @rownum:=0) b
-- 分组并排序                 
SELECT tion.分组字段名称,@last := IF(@first =tion.分组字段名称, @last + 1, 1),@first := tion.分组字段名称
FROM 目标表名称 tion,
     (SELECT
          @last := 0,
          @first := NULL) c
    ORDER BY 分组字段名称, 排序字段名称

视图

--Oracle
create or replace view
    viewName as select * from (select column1,column2 from tableName);
-- mysql
drop view if exists viewName;
create or replace view viewName as select * from tableName;
--可以做个中间视图

一个表数据更新另一张表的数据

--oracle
update tableName1 tn set (tn.column1,tn.column2,tn.column3)
    =
(select tm.column1,tm.column2,tm.column3 from tableName2 tm where tn.columnId=tm.columnId and tm.column4='') 
    where tn.column4='';
-- MySQL                      
update tableName1 tn inner join (select tm.column1,tm.column2,tm.column3,tm.columnId,tm.column4 from tableName2) a 
    on tn.columnId=a.columnId and a.column4='' 
set tn.column1=a.column1,
    tn.column2=a.column2,
    tn.column3=a.column3 
where tn.column4=''; 

varchar、varchar2
- varchar计算的是字符数
- varchar2计算的是字节数
mysql中一个汉字占三个字节，Oracle中一个汉字占两个字节
null和’’
- Oracle中null和’’是一样的
- MySQL中null是null，’’是’’，不一样
date时间类型问题

Oracle中date类型可以包含时分秒，MySQL中date没有时分秒，有时分秒需要使用datetime

函数创建

--oracle                      
create [or replace] function funcName([param]) return resultType as|is 
begin                         
return result;                
end;                          
-- MySQL                      
create function funcName(param) returns dataType 
begin                         
return result;                
end;

函数中变量赋值

--oracle                     
a:='';                       
-- MySQL                     
set a='';                    
--共有的赋值方式             
select sysdate into times from dual;

声明
- MySQL声明在函数体中，也就是在begin之后，用declare关键字

declare stemp varchar(4000);

-   Oracle声明写在as或者is之后，begin之前，不用declare

stemp varchar(4000);

-   在Oracle中as和is是没有区别的

游标

--oracle                      
--声明游标 select后不能接into 
declare cursor 游标名 is select param1,param2 from tableName;
--打开游标                    
open 游标名                   
--提取数据                    
fetch 游标名 into 变量名1，变量名2； 
--关闭游标                    
close 游标名                  
                              
--MySQL                       
-- 定义                       
declare 游标名称 cursor for 查询语句 
-- 打开游标                   
open 游标名称                 
-- 取值                       
fetch 游标名称 info var_name[,var_name]...  
-- 关闭游标                   
close 游标名称                
-- 释放游标                   
deallocate 游标名称

循环

--oracle                      
--loop                        
declare i number
begin
    i:=0;
loop
exit when(i>5);
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
--while
    declare i number
begin
    i:=0;
while i<5 loop
            i:=i+1;
dbms_output.put_line(i);
end loop;
end;
--for
    declare cursor userRows is select * from emp;
begin
for userRow in userRows
     loop
         dbms_output.put_line(userRow.ENAME);
end loop;
end;

-- mysql                      
-- while                      
begin declare i int default 0;
while i<=10
do
set i = i + 1;
end while;
end;                          
     -- loop                       
begin declare i int default 0;
loop_name:loop              
    if i>=10 then leave loop_name;
set i = i + 1;
end loop                   
     end;                          
-- repeat                     
begin declare i int default 0;
repeat
set i = i + 1;
until i>=10 end repeat;
end;