[数据库迁移] ORACLE到MYSQL经验总结

最近工作中有个项目需要做数据库迁移，ORACLE到MYSQL,WEBLOGIC到TOMCAT.

原因是ORACLE和WEBLOGIC都是收费的，降低项目成本。

经过半个月的工作总算初见成效，以下是工作中的经验总结。

1.转移数据库文件（表结构，序列，视图，索引，数据）

    做数据库转换最原始的方法是导出ORACLE数据库文件*.sql脚本然后自己对照ORACLE和MYSQL的差异之处自己修改，不推荐。

    其实有很多工具都支持这种数据库转换：NAVICAT做数据传输，MySQLMigrationTool 等等；

    这里主要推荐Convert Oracle to Mysql 这款工具，极其简单易用。

   

    只需要填好要转换的ORACLE数据库配置，以及预先创建好的MYSQL数据库连接属性就可以了，网上有很多教程。

    我们都知道，ORACLE中有的字段和MYSQL是不一样的，这款工具会自动转换字段属性并填充数据。比如ORACLE中的VARCHAR2,DATE，INT分别对应了MYSQL的VARCHAR,TIMESTAMP,DECIMAL等等。注：有些表(含有特殊属性字段，blob等)可能不会转换成功的，需要自己手动转换！

下面是ORACLE和MYSQL主要差异：

编号

	类别	ORACLE	MYSQL	注释
1	创建存储过程语句不同	create or replace procedure P_ADD_FAC(    id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE)is	DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`; create procedure P_ADD_FAC(        id_fac_cd  varchar(100))	1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程.    oracle使用create or replace.   mysql使用先删除老的存储过程,然后再创建新的存储过程. 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程.    Mysql  存储过程中不可以定义多个存储过程. 3. oracle中字符串类型可以使用varchar2.     Mysql 需要使用varchar 4. Oracle中参数varchar长度不是必须的,    Mysql中参数varchar长度是必须的, 比如varchar(100)
2	创建函数语句不同	CREATE OR REPLACE FUNCTION F_ROLE_FACS_GRP(      ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE     ) RETURN VARCHAR2	DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`; CREATE  FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`(  ii_role_int_key INTEGER(10) ) RETURNS varchar(1000)	1.在创建函数时如果存在同名的函数,会删除老的函数.    oracle使用create or replace.   mysql使用先删除老的函数,然后再创建新的函数. 2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数.    Mysql  Functions不可以定义多个函数. 3.  oracle返回值用return.     Mysql返回值用returns.
3	传入参数写法不同	procedure P_ADD_FAC(    id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE)	create procedure P_ADD_FAC(      (in) id_fac_cd  varchar(100))	1. oracle存储过程参数可以定义为表的字段类型.    Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度. 2. oracle 参数类型in/out/inout写在参数名后面.    Mysql  参数类型in/out/inout写在参数名前面. 3. oracle 参数类型in/out/inout 都必须写.    Mysql  参数类型如果是in,则可以省略. 如果是out或inout则不能省略. 注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数） RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
		function func_name(            gw_id  in(out)  varchar2 )	create function func_name(        gw_id varchar（100))
4	包的声明方式	create or replace package/package body package name	拆分成多个存储过程或函数	oracle可以创建包,包中可以包含多个存储过程和方法. mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 例1: 方法命名 oracle 中SD_FACILITY_PKG.F_SEARCH_FAC to mysql SD_FACILITY_F_SEARCH_FAC 例2: 过程命名 oracle 中SD_FACILITY_PKG.P_ADD_FAC to mysql SD_FACILITY_P_ADD_FAC
5	存储过程返回语句不一样	return;	LEAVE proc; (proc 代表最外层的begin end)	oracle存储过程和方法都可以使用return退出当前过程和方法. Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. Mysql方法可以使用return退出当前方法.
6	存储过程异常处理不一样	EXCEPTION     WHEN OTHERS THEN     ROLLBACK ;     ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'||         TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);	DECLARE EXIT HANDLER FOR  SQLEXCEPTION  BEGIN     ROLLBACK ;     set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):',         TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));  END;	oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分.  oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获. mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能.           目前mysql不支持自定义异常.
7	过程和函数的声明变量的位置不同	声明变量在begin…end体之前	声明变量在begin...end体内，begin之后其他任何内容之前
8	NO_DATA_FOUND异常处理	EXCEPTION         WHEN NO_DATA_FOUND THEN             oi_rtn_cd := 1;             ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016',                                                  li_sub_rtn_cd,                                                  lv_sub_rtn_msg                                                  );	使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释.	oracle中: NO_DATA_FOUND是游标的一个属性. 当select没有查到数据就会出现 no data found 的异常，程序不会向下执行. Mysql: 没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑.
9	在存储过程中调用存储过程方式的不同	Procedure_Name(参数);	Call Procedure_Name(参数);	MYSQL存储过程调用存储过程，需要使用Call pro_name(参数).  Oracle调用存储过程直接写存储过程名就可以了.
10	抛异常的方式不同	RAISE Exception_Name;	见备注	详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分

具体的函数差异就不一一列举，关于树的递归查询，ORACLE提供了START WITH ... CONNECT BY PRIOR ...函数，MYSQL没有提供对应函数，我们用一个存储过程解决MYSQL递归遍历树的问题:

CREATE DEFINER = `skpdb`@`%` FUNCTION `getChildList`(rootId VARCHAR(32))
 RETURNS varchar(1000)
BEGIN 
           DECLARE pTemp VARCHAR(1000);  
           DECLARE cTemp VARCHAR(1000); 
          
           SET pTemp = '$';    
           SET cTemp = rootId;
           WHILE cTemp is not null DO  
             SET pTemp = concat(pTemp,',',cTemp);  
             SELECT group_concat(ZZJG_DM) INTO cTemp FROM PT_DM_ZZJG   
             WHERE FIND_IN_SET(SJ_ZZJG_DM,cTemp)>0; 
           END WHILE;  
           RETURN pTemp;  
END

这个函数的作用是传入节点ID，会返回以这个节点为根节点的子树所有节点ID以逗号分隔，如上图。关于FIND_IN_SET的作用网上有恨多说明。

最后，数据库转换工具只会转换表结构和数据，其他的比如视图，索引，序列，存储过程，function需要自己手动修改添加。

MYSQL的存储过程没有包的概念，如果取名xxx.xxxx也会call不成功。如下图: