Oracle到MySQL的迁移步骤及各种注意事项

最近公司一个项目需要将数据库进行一次迁移,从Oracle到mysql,网上资料甚少,现将我本次迁移过程中所遇到的一些问题总结于此(主要是存储过程的迁移),希望能给自己做一个日后的参考,如果有幸能帮助到大家更好。

-- mysql中没有包的概念,因此迁移的时候将存储过程命名为'包名.存储过程名'的格式

mysql存储过程格式:

DELIMITER $$ -- 分隔符

-- CREATE PROCEDURE([[IN |OUT ] 参数名 数据类型...]) ,IN和OUT写在最前面,其中IN可以省略

CREATE PROCEDURE `pkg_ypgl.prc_ypsc`(

prm_ypbm VARCHAR (20),

OUT prm_AppCode VARCHAR (20),

-- 程序执行代码

OUT prm_ErrorMsg VARCHAR (100)

-- 程序执行错误信息

)

BEGIN

/*变量定义*/

DECLARE n_count DECIMAL (8) ;

DECLARE done INT(10);

 

/*设置游标结束标志*/

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 如果NOT FOUND,取不到值,则将done赋值1,并且程序继续执行

SET done=0;

 

/*定义一个区块lavel_error,逻辑错误处理*/

label_error : BEGIN

/*定义游标*/

DECLARE cur_bdjl CURSOR FOR

SELECT .....

/*打开游标*/

OPEN cur_bdjl ;

REPEAT

FETCH cur_bdjl INTO v_aaz001....

IF NOT done THEN -- 如果结束标志done为0则继续循环

........

END IF;

/*结束循环,关闭游标*/

UNTIL done -- 直到NOT FOUND

END REPEAT ;

CLOSE cur_bdjl ;

 

SET prm_AppCode = 'noerror' ; -- 将prm_AppCode设为正确

SET prm_ErrorMsg = '' ;

END;

 

END$$

DELIMITER ;

 

 

 

 

数据类型:

Oracle:varchar2 Mysql:varchar(20) (参数自设)

Oracle:number() Mysql:decimal()

Oracle:date Mysql:datetime

 

定义变量:

Mysql需要在每句前面加DECLARE

 

给变量赋值:

Oracle:v_string := ‘asdas’; Mysql: SET string := ‘asdas’; (等号前面的冒号可以有也可以没有)

 

异常处理:

Oracle:EXCEPTION WHEN OTHERS THEN….

Mysql: DECLARE { EXIT | CONTINUE } HANDLERFOR { error-number | { SQLSTATE error-string } | condition } SQL statement;

SQLWARNING 代表所有以01开头的错误代码

NOT FOUND 代表所有以02开头的错误代码,也包括游标结束的时候

SQLEXCEPTION 代表除了SQLWARNING和NOT FOUND 的所有错误代码

eg. DECLARE EXIT HANDLER FORSQLEXCEPTION,SQLWARNING,NOT FOUND SET a = 1;

注:一个begin....end里面只能声明一个HANDLER,EXIT表示遇到这种异常时就执行SET a = 1然后结束这个存储过程,CONTINUE表示遇到这种异常时就SET a = 1,然后继续执行之后的存储过程

 

跳转:

Oracle: GOTO label_error;

…..

<<label_error >>

Mysql:初始化错误代码prm_AppCode为“错误”,定义一个区块label_error,在区块的最后将prm_AppCode set为’noerror’,中间触发条件,将GOTO label_error;改写成leave label_error;跳出区块

 

 

游标:

Mysql只有静态游标,没有动态游标,用存储过程代替

定义游标的语句为DECLAREcur_bdjl CURSOR FOR …..

Mysql不支持rec_curname.aaz001这种写法,所以必须将游标取得的所有字段FETCH INTO 到变量里

 

 

循环:

Mysql里有三种循环方式

(1).WHILE循环

WHILE expression DO

statements

END WHILE;

(2).LOOP循环

LOOP

statements

END LOOP;

(3).REPEAT UNTIL循环

REPEAT

statements

UNTIL expression

END REPEAT;

 

 

序列:

Mysql中没有序列,用函数+表的方法取代.

 

建表语句:

CREATE TABLE `seq` (
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '序列号生成器名称',
`val` bigint(20) unsigned NOT NULL COMMENT '序列号',
`increment` int(4) DEFAULT '1' COMMENT '序列的增量',
`min` bigint(20) DEFAULT NULL COMMENT '序列最小值',
`max` bigint(20) DEFAULT NULL COMMENT '序列最大值',
`cycle` char(1) DEFAULT 'N' COMMENT '是否循环',
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mysql模拟序列号生成器用表'

 

函数如下(自己写的,可能有错):

DELIMITER $$

DROP FUNCTION IF EXISTS `seq`$$

CREATE FUNCTION `seq`(seq_name VARCHAR(20))RETURNS BIGINT(20)

BEGIN

DECLARE v_value BIGINT(20);

DECLARE v_CYCLE CHAR;

DECLARE v_MIN BIGINT(20);

DECLARE v_MAX BIGINT(20);

SELECT a.val,a.MIN,a.MAX,a.CYCLE INTOv_value,v_MIN,v_MAX,v_CYCLE FROM seq a WHERE NAME = seq_name;

IFv_CYCLE = 'Y' AND v_value = v_MAX THEN -- 该序列为循环且当前值为其最大值

UPDATE seq -- 将当前值设为 v_MIN

SET val = v_MIN

WHERE NAME = seq_name;

ELSE

UPDATE seq -- 否则将当前值设为val + increment

SET val = val + increment

WHERE NAME = seq_name;

END IF;

SELECT val INTO v_value FROM seqWHERE NAME = seq_name;

RETURN v_value;

END$$

DELIMITER ;

 

 

 

 

更新:

Oracle: UPDATE TABLE T SET (A,B,C) = (SELECT A,B,C FROM TABLE_2 K WHERE K.Y =T.Y) WHERE T.X = V_X;

Mysql: UPDATE TABLE T,TABLE_2 K SET T.A =K.A,T.B=K.B,T.C=K.C WHERE K.Y = T.Y ANDT.X = V_X

 

GROUP BY:

mysql的group by 语句可以select 没有被分组的字段,如

select id,name,age from A group by age

这个取出的id,name所在的行是每个分组中的第一行数据

 

调用:

Mysql: call procedure_name(所有参数);

 

 

跳出循环:

Oracle: EXIT;

Mysql: 将循环的内容定义为一个区块label_loop,需要跳出循环时则 leave label_loop;

 

 

注释:

1、#注释内容

2、-- 注释内容 注意-- 后需要加一个空格

3、块注释用/*注释内容*/

 

 

表的注释:

在oracle中执行如下语句:

select 'altertable '||table_name||' comment'||' '''||COMMENTS||' '''||';'fromUSER_TAB_COMMENTSwhere commentsis not null;

将得到的结果放到mysql中执行即可添加表名的注释

 

 

表的字段注释:

在oracle中执行如下语句:

select distinct(data_type) FROM all_tab_columnswhere owner='YDMIS'

将查询出的本次转换涉及到的数据类型用decode函数转换为Mysql中对应函数(参数)的形式,如将CHAR转换为CHAR(20),参数的值在all_tab_columns的DATA_LENGTH DATA_PRECISION DATA_SCALE中取得。

在oracle中执行如下语句,注意decode函数里的参数需根据上一步的查询结果转换:

select 'alter table '||a.table_name||' modify column '||a.column_name||' '||decode(b.data_type,'VARCHAR2','VARCHAR('||b.DATA_LENGTH||')','DATE','DATETIME','NUMBER','DECIMAL('||b.DATA_PRECISION||','||b.DATA_SCALE||')','CHAR','CHAR('||b.DATA_LENGTH||')','LONGRAW','mediumblob')||' comment '||''''||comments||''''||';'

from user_col_comments a,all_tab_columns b

where a.comments is not null

and a.table_name = b.table_name

and a.column_name = b.column_name

and b.owner = 'YDMIS';

将得到的结果放到mysql中执行即可添加表字段的注释

 

 

函数:

功能oraclemysql备注eg.oracleeg.mysql
连接字符串||concat()'a'||'b'||'c'concat( 'a','b','c')
将其他格式转换为字符串concat(x,'')
截取字符串substr()substring()substr('abcd',1,3)substring('abcd',1,3)
string转换为dateto_date()str_to_date(str, format)to_date(aae036,'yyyy-mm-dd hh24:mi:ss')str_to_date(aae036,'%Y-%m-%d %H:%i:%s')
获取当前日期sysdatenow(),sysdate(),current_datenow()返回的是程序开始执行时的时间,sysdate()返回实时时间,
一般用now()
current_date表示当前的年月日
取出日期的指定部分date_format(date,type)%Y:年
%c:月
%d:日
%H:小时
%i:分钟
%s:秒
date_format(now(),'%Y-%c-%d %h:%i:%s')
增加一天sysdate+1DATE_ADD(date,INTERVAL expr type)sysdate+1DATE_ADD(now() ,INTERVAL 1 DAY)
类型转换TO_CHAR
TO_DATE
TO_NUMBER
cast(xxx as type)type:二进制 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
to_char(33)cast(33 as char(2))
精度转换to_number(x,type)round(x,d)保留到小数点后d位,而第d位的保留方式为四舍五入。若要保留x值小数点左边的d位,可将d设为负值
替换空值NVL( string1, replace_with)ifnull(string1, replace_with)
decode()decode (expression, search_1, result_1, default)case expression when search_1 then result_1 else default end

相关推荐