Oracle数据库迁移到Mysql过程中类型、函数、存储过程中的变化

Oracle数据库迁移到Mysql过程中类型、函数、存储过程中的变化

一、常见数据类型的区别

Oracle	Mysql	使用区别
NUMBER	int/bigint	oracle：任何长度都可以 mysql:当数值长度过大时应使用bigint
VARCHAR2	varchar	oracle：VARCHAR2(20) mysql：varchar(20)
NUMBER(12,4)	decimal(12,4)	都表示为最大长度12位，保留4位小数
DATE	datetime	都表示为时间类型,oracle中的DATE 等于mysql中的datetime

二、常见函数的区别

Oracle	Mysql	使用区别
nvl	ifnull	oracle：NVL(t.name,‘小王’) mysql：IFNULL(t.name,‘小王’) 意义相同，表示如果t.name为空则结果为’小王’
substr	substring	oracle：substr(t.user_type,1,2) mysql：substring (t.user_type,1,2) 意义相同，表示字符串从第一位开始截取两位
decode	case when	oracle：decode(t.sex,‘1’,‘男’,‘2’,‘女’,‘未知’) mysql：(case when t.sex=‘1’ then ‘男’ when t.sex=‘2’ then ‘女’ else ‘未知’ end) 意义相同，表示如果t.sex等于1返回结果为’男’， t.sex等于2返回结果为’女’，如果都不是返回未知
to_char	date_format	oracle：to_char(sysdate, ‘yyyymmdd’) mysql：date_format(now(), ‘%Y%m%d’) 意义相同，表示获取当前时间按照年月日显示
to_date	str_to_date	oracle：to_date(‘20211027192830’,‘yyyy-mm-dd hh24:mi:ss’) mysql：str_to_date(‘20211027192830’,’%Y-%m-%d %H:%i:%s’) 意义相同，表示将字符串按照参数2的日期格式返回
sysdate	now()	都表示当前时间
to_number	cast(’’ as signed integer) from dual	oracle：to_number(‘123456’) mysql：cast(‘123456’ as signed integer) 意义相同，表示为将字符串转换为数字类型

三、序列

oracle中可以直接创建序列

-- 创建序列
create sequence seq_batch_id
minvalue 1
maxvalue 99999999999
increment by 1;

-- 使用序列
select seq_batch_id.nextval from dual;

而mysql本身不提供序列，需要手动去创建

-- 1.创建序列表
create table if not exists `sequence` (  
  `name` varchar(50) not null,  
  `current_value` int(11) not null,  
  `increment` int(11) not null default '1'  
) ;

-- 2.创建函数，返回当前序列值 
create create `currval`(seq_name varchar(50)) returns int(11)  
    reads sql data  
    deterministic  
begin  
declare value integer;  
set value = 0;  
select current_value into value from sequence where name = seq_name;  
return value;  
end ;

-- 3.将序列值递增，然后返回当前序列值
create function `nextval`(seq_name varchar(50)) returns int(11) 
    deterministic    
begin    
update sequence set current_value = current_value + increment where name = seq_name ;
return currval(seq_name);  
end ;

-- 4.插入一条序列(后面增加序列直接更换序列名插入即可)
insert into `sequence` (`name`, `current_value`, `increment`) values  ('seq_batch_id', 0, 1);

-- 5.使用序列
select nextval("seq_batch_id") from dual

四、自定义函数

作用	oracle	mysql	区别
创建函数	create or replace function f_return_nucleic	drop function if exists `f_return_nucleic`; create function `f_return_nucleic`()	本质上没有区别，oracle表示为如果没有进行创建如果有则替换，mysql表示先删除原函数再创建。
参数	create or replace function f_return_nucleic(name in varchar2)	create function `f_return_nucleic`(name varchar(20))	无参情况下： oracle可以直接创建，不需要写括号，而mysql必须写括号。 有参情况下： oracle：参数名称+in+类型 mysql：参数名称+类型 注意：mysql的varchar类型必须带长度
返回	return varchar2	returns varchar(20)	都写在begin之前，区别是oracle用return，而mysql用returns

五、存储过程

作用	oracle	mysql	区别
创建存储过程	create or replace procedure p_sd_return_nucleic	drop procedure if exists `p_sd_return_nucleic `; create procedure `p_sd_return_nucleic`	与函数相同，oracle表示为如果没有进行创建如果有则替换，mysql表示先删除原过程再创建。
参数	create or replace procedure p_sd_return_nucleic(iv_no in number,iv_date in varchar2,ov_resultcode out number,ov_resulterrinfo out varchar2)	drop procedure if exists `p_sd_return_nucleic`; create procedure`p_sd_return_nucleic`(in iv_no int,in iv_date varchar(20),out ov_resultcode int,out ov_resulterrinfo varchar(200))	与函数相同，无参情况下： oracle可以直接创建，不需要写括号，而mysql必须写括号。 有参情况下： oracle：参数名称+in+类型 mysql：参数名称+类型 注意：mysql的varchar类型必须带长度
声明变量	is v_pro_id varchar2(20); v_date varchar2(100);	declare v_pro_id varchar(20); declare v_date varchar(100);	oracle的声明变量在begin之前，而mysql的声明变量在begin之后
变量赋值	v_pro_id := ‘bank’; v_date := iv_date;	set v_pro_id = ‘bank’; set v_date = iv_date;	都在begin之后，mysql需要使用set进行赋值
返回	return	top: begin … leave top;	oracle可以直接使用return返回，而mysql的存储过程不提供return关键字返回，只能通过leave返回，将top定义在begin之前，返回使用leave top;
判断	if…else…elsif…end if	if…else…elseif…end if	这里很多时候不好区分，作用相同，区别只差一个字母
返回错误信息	SQLERRM	mysql中不具备此功能	Oracle内置函数SQLERRM，用来返回错误消息
返回错误编码	SQLCODE	mysql中不具备此功能	Oracle内置函数SQLCODE，用来返回错误编码

六、常用日期转换

oracle	mysql	表现形式
yymmdd	%y%m%d	oracle：to_char(sysdate,‘yymmdd’) mysql：date_format(now(),’%y%m%d’) 得到的数为：‘211030’
yyyymmdd	%Y%m%d	oracle：to_char(sysdate,‘yyyymmdd’) mysql：date_format(now(),’%Y%m%d’) 得到的数为：‘20211030’
yyyy-mm-dd hh24:mi:ss	%Y-%m-%d %H-%m-%s	oracle：to_char(sysdate,‘yyyy-mm-dd hh12:mi:ss’) mysql：date_format(now(),’%Y-%m-%d %h-%m-%s’) 得到的数为：‘2021-10-30 04:48:15’ 注意mysql中的H要大写

七、其他

oracle	mysql	说明
‘0’||’%’	concat(‘0’,’%’)	Oracle拼接字符串可以直接用’||' 而mysql拼接需要使用函数concat()
to_char(‘123’,‘FM099999’)	lpad(‘123’,6,‘0’)	字符串不足6位的自动补全6位
rownum	@rownum:=@rownum+1 as rownum	按顺序排序后返回序号
select rownum, t.* from t_s_log t	select @rownum := @rownum + 1 as rownum, t.* from (select @rownum := 0,t.* from t_s_log t) t	查询的使用方法

八、oracle中row_number() over partition by 转换mysql

oracle中row_number() over partition by 转换mysql