系统移植方案.doc

文档名称: BPM ON MYSQL系统移植方案严格保密，仅用于BPM项目文档作者：cyt2005 提交时间：2007年7月06日系统移植方案Mysql的版本是mysql-5.1.19-win32操作系统是WINXP21.1 移植过程中重点问题1.1.1 数据类型差异ORACLE数据库和MYSQL数据库在数据类型方面差异比较大，而且数据类型也是一个数据库存储数据的基础，所以找到数据类型之间的对应是整个系统进行移植的基础。以下给出了ORACLE MYSQL数据类型的对应关系。数值类型： NUMBER DECIMAL，精度刻度都不变注：如果是序列用BIGINT字符串类型： VARCHAR2 VARCHAR长度不变。 LONG LONGTEXT这里有可能遇到的问题是超过主键key长度的问题，根据实际情况适当修改，如果是TEXT类型也需要指名长度，否则建立key会报错日期类型： DATE DATETIME TIMESTAMP(N) TIMESTAMP 1.1.2 SQL语法差异SEQUENCE：MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：1、 作为表中自增字段的序列号。2、 程序中获得自动编号。MYSQL数据类型中存在 AUTO_INCREMENT为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能。1、 对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现。2、 ORACLE开发的应用程序中直接SELECT SEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现。首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：mysql SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 3 |+-+1 row in set (0.06 sec)我们可以创建一个含有自增列的表，对该表进行INSERT操作后，再利用LAST_INSERT_ID()函数来获得刚刚INSERT的值，也就是相当于ORACLE中的SEQUENCE.NETVAL。也就是INSERT操作+SELECT操作获得一个自动编号。mysql CREATE TABLE MOCHA_BE_SEQUENCE(ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT);Query OK, 0 rows affected (0.63 sec)mysql INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.09 sec)mysql SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 1 |+-+1 row in set (0.05 sec)mysql INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.06 sec)mysql SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 2 |+-+1 row in set (0.00 sec)BLOG：ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。视图Mysql视图限制（1）SELECT语句不能包含FROM子句中的子查询。（2）SELECT语句不能引用系统或用户变量。（3）SELECT语句不能引用预处理语句参数。（4）在存储子程序内，定义不能引用子程序参数或局部变量。（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。（7）在视图定义中命名的表必须已存在。（8）不能将触发程序与视图关联在一起。我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。例子：Oracle中带子查询的视图：CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW ASSELECT USER_ID AS ID, USER_NAME AS NAME, CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE,00000001.10000000), .00.), PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,0) AS CODE, NVL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, PERSON AS REC_TYPEFROM (SELECT PERSON.USER_ID , PERSON.USER_NAME, NVL(POSITION.ORG_ID, -1) AS ORG_ID, NVL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, NVL(POSITION.PERSON_LEVEL, 64) AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON WHERE PERSON.USER_ID = POSITION.USER_ID (+) AND PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=A) PERSON_POSITION, MOCHA_IM_ORG_VIEW ORGWHERE PERSON_POSITION.ORG_ID = ORG.ORG_ID (+)UNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.20000000.00.64000000000 AS CODE, 1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=IUNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.30000000.00.64000000000 AS CODE,1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = 1 AND STATUS=AUNION ALLSELECT LPAD(ORG_ID,20, ) AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, ORG AS REC_TYPE FROM MOCHA_IM_ORG_VIEWUNION ALL SELECT -1 AS ID, 未分派人员 AS NAME, 00000001.10000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALL SELECT -2 AS ID, 待删除人员 AS NAME, 00000001.20000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALL SELECT -3 AS ID, 系统管理员 AS NAME, 00000001.30000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE From DUAL/转为mysql：CREATE OR REPLACE VIEW PERSON_POSITION ASSELECT PERSON.USER_ID , PERSON.USER_NAME, IFNULL(POSITION.ORG_ID, -1) AS ORG_ID, IFNULL(POSITION.ORG_PERSON_NO, 0) AS ORG_PERSON_NO, IFNULL(POSITION.PERSON_LEVEL, 64) AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN MOCHA_IM_PERSON PERSON ON PERSON.USER_ID = POSITION.USER_ID WHERE PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=A;/CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW ASSELECT USER_ID AS ID, USER_NAME AS NAME,CONCAT(CONCAT(CONCAT(IFNULL(ORG.ORG_CODE,00000001.10000000), .00.),PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,0) AS CODE, IFNULL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_ORG_VIEW ORG LEFT JOIN PERSON_POSITION ON PERSON_POSITION.ORG_ID = ORG.ORG_ID UNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.20000000.00.64000000000 AS CODE, 1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = 0 And PERSON.STATUS=IUNION ALLSELECT USER_ID AS ID, USER_NAME AS NAME, 00000001.30000000.00.64000000000 AS CODE,1 AS REC_LEVEL, PERSON AS REC_TYPEFROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = 1 AND STATUS=AUNION ALLSELECT LPAD(ORG_ID,20, ) AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, ORG AS REC_TYPE FROM MOCHA_IM_ORG_VIEWUNION ALL SELECT -1 AS ID, 未分派人员 AS NAME, 00000001.10000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALL SELECT -2 AS ID, 待删除人员 AS NAME, 00000001.20000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALL SELECT -3 AS ID, 系统管理员 AS NAME, 00000001.30000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE From DUAL/触发器，函数，存储过程语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql控制流程等等。对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。就是说mysql不支持oracle中create trigger * before insert or Update or delete on *.此时应该把这个触发器拆分为3个触发器。例子：Oracle下触发器：CREATE OR REPLACE TRIGGER MOCHA_IM_DUTY_TRGBEFORE INSERT OR UPDATE OR DELETE ON MOCHA_IM_DUTYFOR EACH ROWDECLAREbefImg VARCHAR2(2000);afterImg VARCHAR2(2000);action VARCHAR2(10);action_module VARCHAR2(10);info_category VARCHAR2(20);BEGINaction_module := IM;info_category := duty;befImg := DUTY_ID | :old.duty_id | DUTY_NAME | :old.duty_name;afterImg := DUTY_ID | :new.duty_id | DUTY_NAME | :new.duty_name;IF (:old.duty_id is null) THENaction := Insert;befImg := ;INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);ELSIF (:new.duty_id is null) THEN action := Delete;afterImg := ;INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);ELSEaction := Update;IF(befImg != afterImg) THEN INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);END IF;END IF;EXCEPTION WHEN OTHERS then befImg := ;end;/mysql下：CREATE TRIGGER MOCHA_IM_DUTY_TRG_INSERTBEFORE INSERT ON MOCHA_IM_DUTYFOR EACH ROWBEGINDECLARE befImg VARCHAR(2000);DECLARE afterImg VARCHAR(2000);DECLARE action VARCHAR(10);DECLARE action_module VARCHAR(10);DECLARE info_category VARCHAR(20);DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUNDbeginset befImg = ;end;set action_module = IM;set info_category = duty;set afterImg =CONCAT( DUTY_ID , new.duty_id , DUTY_NAME , new.duty_name);set action = Insert;set befImg = ;INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);end;/CREATE TRIGGER MOCHA_IM_DUTY_TRG_DELBEFORE DELETE ON MOCHA_IM_DUTYFOR EACH ROWBEGINDECLARE befImg VARCHAR(2000);DECLARE afterImg VARCHAR(2000);DECLARE action VARCHAR(10);DECLARE action_module VARCHAR(10);DECLARE info_category VARCHAR(20);DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUNDbeginset befImg = ;end;set action_module = IM;set info_category = duty;set befImg = CONCAT(DUTY_ID , old.duty_id , DUTY_NAME , old.duty_name);set action = Delete;set afterImg = ;INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);end;/CREATE TRIGGER MOCHA_IM_DUTY_TRG_UPDATEBEFORE UPDATE ON MOCHA_IM_DUTYFOR EACH ROWBEGINDECLARE befImg VARCHAR(2000);DECLARE afterImg VARCHAR(2000);DECLARE action VARCHAR(10);DECLARE action_module VARCHAR(10);DECLARE info_category VARCHAR(20);DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUNDbeginset befImg = ;end;set action_module = IM;set info_category = duty;set befImg = CONCAT(DUTY_ID , old.duty_id , DUTY_NAME , old.duty_name);set afterImg =CONCAT( DUTY_ID , new.duty_id , DUTY_NAME , new.duty_name);IF (old.duty_id is null) THENset action = Insert;set befImg = ;ELSEIF (new.duty_id is null) THEN set action = Delete;set afterImg = ;ELSEset action = Update;IF(befImg != afterImg) THEN INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);END IF;END IF;end;/在声过程和函数时注意过程支持inout参数函数不支持，mysql下函数是returnsOracle下过程：create or replace procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR2)ISbeginexecute immediate insert into MOCHA_FE_DOC_BODY_TEMP |v_sql;end MOCHA_FE_OPEN_DOC_SP;/Mysql下：create procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR(200)beginset sqltext=CONCAT( insert into MOCHA_FE_DOC_BODY_TEMP ,v_sql);prepare stmt from sqltext;execute stmt;end ;/Oracle下函数：CREATE OR REPLACE FUNCTION GET_PER_NAME_LEVEL(v_UserId VARCHAR2)RETURN VARCHAR2 ISv_UserName VARCHAR2(30);v_PrimaryPosition VARCHAR2(1);v_PersonLevel VARCHAR2(2);v_LevelName VARCHAR2(4000);v_Result VARCHAR2(4000);v_RecCount NUMBER;CURSOR c_PersonPosition ISSELECT PERSON.USER_NAME, POSITION.PERSON_LEVEL, POSITION.PRIMARY_POSITION, PERSON_LEVEL.LEVEL_NAMEFROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVELWHERE PERSON.USER_ID = POSITION.USER_ID AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL AND POSITION.USER_ID = v_UserIdORDER BY POSITION.PRIMARY_POSITION DESC;BEGIN SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId; IF v_RecCount=0 THEN SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON WHERE USER_ID = v_UserId; ELSE OPEN c_PersonPosition; LOOP FETCH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName; EXIT WHEN c_PersonPosition%NOTFOUND; v_Result := NVL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), ); IF (v_PrimaryPosition = 1 AND (v_RecCount 1 OR SUBSTR(v_PersonLevel,2) 0 AND v_PrimaryPosition = 0) THEN v_Result := CONCAT(CONCAT(v_Result, 兼), v_LevelName); END IF; END LOOP; CLOSE c_PersonPosition; IF LENGTH(v_Result)0 THEN v_Result := CONCAT(v_Result, ); END IF;IF SUBSTR(v_Result, LENGTH(v_Result)-1) = THEN v_Result := SUBSTR(v_Result,1,LENGTH(v_Result)-2);END IF; END IF; RETURN v_Result;END;Mysql下函数：CREATE FUNCTION GET_PER_NAME_LEVEL(v_UserId VARCHAR(30)RETURNS VARCHAR(30)BEGIN DECLARE v_UserName VARCHAR(30);DECLARE v_PrimaryPosition VARCHAR(1);DECLARE v_PersonLevel VARCHAR(2);DECLARE v_LevelName VARCHAR(4000);DECLARE v_Result VARCHAR(4000);DECLARE v_RecCount DECIMAL;DECLARE c_PersonPosition CURSOR FORSELECT PERSON.USER_NAME, POSITION.PERSON_LEVEL, POSITION.PRIMARY_POSITION, PERSON_LEVEL.LEVEL_NAMEFROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVELWHERE PERSON.USER_ID = POSITION.USER_ID AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL AND POSITION.USER_ID = v_UserI