Oracle到mysql转换地问题分析总结.doc-得力文库

资源描述

《Oracle到mysql转换地问题分析总结.doc》由会员分享，可在线阅读，更多相关《Oracle到mysql转换地问题分析总结.doc（44页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、-_Oracle 到到 mysql 转换的问题总结转换的问题总结常用字段类型区别常用字段类型区别oraclemysqlnumber（10,0）intnumber（10,2）decimal（10,2）varchar2varchardatedatetimeColbtext个别语句写法区别个别语句写法区别1.oracle 里只可以用单引号包起字符串，mysql 里可以用双引号和单引号。 2.mysql 在 select * from () .，from 后面是一个结果集时，括号后面必须加上别名。 3.mysql 在 delete 数据时不能给表加别名，如：delete from table1 T

2、where.，会报错，但是可以这样写：delete T from table1 T where.。 4.Mysql 不支持在同一个表中先查这个表在更新这个表，举个例子说明一下，insert into table1table1 values（字段 1，（select 字段 2 from table1table1 where.）），但是可以在后面那个 table1 加上别名就没有问题了。 insert into table1table1 values（字段 1，（select T.字段 2 from table1table1 T T where.）） 5.MYSQL 有自动增长的数据类型

3、，插入记录时不用操作此字段，会自动获得数据值。ORACLE 没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。也可以自定义函数实现 oracle 的 nextval。6.翻页的 sql 语句处理，MYSQL 处理翻页的 SQL 语句比较简单，用 LIMIT 开始位置, 记录个数，例如：select * from table limit m,n，意思是从 m+1开始取 n 条。常见的函数替换常见的函数替换1.日期转换方面的函数 oraclemysql说明-_to_char(date,yyyy-MM-dd hh24:mi:ss)date_forma

4、t(date,%Y- %m-%d %H:%i:%s)注意时间格式的对应to_date(str,yyyy-MM-dd hh24:mi:ss)str_to_date(str,%Y-%m- %d %H:%i:%s)注意时间格式to_date(str,yyyy-MM-dd hh24:mi:ss)str_to_date(str,%Y-%m- %d%T)注意时间格式to_char()、to_number()convert(字段名,类型)类型转换 date + ndate_add(date,interval n day)日期增加 n 天 select date_add(sysdate(),INTER VAL

5、 2 DAY); add_months(date,n)date_add(date,interval n month)日期增加 n 个月 select date_add(sysdate(),INTER VAL 2 MONTH); date1 - date2datediff(date1,date2)日期相减获取天数 2.oracle 中 decode（）函数，可以用 case when 进行替换例子： Oracle：select decode(a,b,c,d) as col1 from table1; Mysql：select case when a=b then c when a!=b the

6、n d end as col1 from table1 3.oracle 的函数 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)，根据 col1 分组，在分组内根据 col2 排序，改函数计算的值就表示每组内部排序后的顺序编号。 Mysql 没有这个函数，可以用 mysql 的用户变量来实现例子： Oracle：select row_number() over(partition by col1 order by col2) as num from table1 Mysql：select num1 as num from (selec

7、t if(pdept=col1,rank:=rank+1,rank:=1) as num1,pdept:=col1 from table1 order by col2 ) H 这里用到 mysql 的用户变量。 4.oracle 的行号 ROWNUM，mysql 没有这个行号，也需要用用户变量来实现。例子： Oracle：select * from table1 where rownum - col1=0 Mysql: select * from table1,(SELECT (rowNum := 0) HH where (rowNum := rowNum + 1) - col1=0 5.O

8、racle 中的 substr （hello,a,b） mysql 中是 substring（hello,a,b） oracle 的 a=0 和 a=1 是一样的，都是从第一个开始。Mysql 是从 0 开始。-_6.Oracle 的 nvl()对应 mysql 的 ifnull()。 7.Oracle 中的 WMSYS.WM_CONCAT（），列转行函数，以逗号隔开，mysql 可以用 GROUP_CONCAT()进行替换。 8.Oracle 的|可以用 mysql 的+替代，但是 mysql 在往某列加上字符时应该用 concat，例如给表中 name 字段加上 x：update t

9、able1 set name=concat（x，name）。1.1 移植过程中重点问题 1.1.1 数据类型差异ORACLE 数据库和 MYSQL 数据库在数据类型方面差异比较大，而且数据类型也是一个数据库存储数据的基础，所以找到数据类型之间的对应是整个系统进行移植的基础。以下给出了 ORACLE MYSQL 数据类型的对应关系。数值类型：NUMBER DECIMAL，精度刻度都不变注：如果是序列用 BIGINT字符串类型：VARCHAR2 VARCHAR 长度不变。LONG LONGTEXT这里有可能遇到的问题是超过主键 key 长度的问题，根据实际情况适当修改，如果是TEXT 类型也需要

10、指名长度，否则建立 key 会报错日期类型：-_DATE DATETIMETIMESTAMP(N) TIMESTAMP1.1.2 SQL 语法差异SEQUENCE：MYSQL 没有 ORACLE 中的 SEQUENCE 对象，我们在迁移的时候需要特别注意，一般 SEQUENCE 有两种用途：1、作为表中自增字段的序列号。2、程序中获得自动编号。MYSQL 数据类型中存在 AUTO_INCREMENT 为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的 SEQUENCE 功能。1、对于 ORACLE 中 SEQUENCE 作为表的自增列一般是通过与触发器绑定实现的，在 M

11、YSQL 中我们可以直接利用 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_

12、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 SEL

13、ECT 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，在存储方

14、面都是一样的，BLOB 列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库 BLOG 类型的操作的差异。视图Mysql 视图限制（1）SELECT 语句不能包含 FROM 子句中的子查询。（2）SELECT 语句不能引用系统或用户变量。（3）SELECT 语句不能引用预处理语句参数。（4）在存储子程序内，定义不能引用子程序参数或局部变量。（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用 CHECK TABLE 语句。（6）在定义中不能引用 TEMPORARY 表，不能创建 TEMPOR

15、ARY 视图。（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.1000

16、0000), .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

17、_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_

18、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 AndPERSON.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_

19、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_VIEW-_UNION ALLSELECT -1 AS ID, 未分派人员 AS NAME, 00000001.10000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION AL

20、LSELECT -2 AS ID, 待删除人员 AS NAME, 00000001.20000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALLSELECT -3 AS ID, 系统管理员 AS NAME, 00000001.30000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPEFrom DUAL/转为 mysql：CREATE OR REPLACE VIEW PERSON_POSITION ASSELECT PERSON.USER_ID , PERSON.USER_NAME

21、, 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;

22、/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_TYPEFR

23、OM 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 AndPERSON.STATUS=IUNION ALLS

24、ELECT 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_O

25、RG_VIEWUNION ALLSELECT -1 AS ID, 未分派人员 AS NAME, 00000001.10000000 AS CODE, 1 AS REC_LEVEL, ORG AS REC_TYPE FROM DUALUNION ALLSELECT -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

26、REC_LEVEL, ORG AS REC_TYPEFrom DUAL/触发器，函数，存储过程语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql 控制流程等等。对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个 BEFORE UPDATE 触发程序。但可以有 1 个BEFORE UPDATE 触发程序和 1 个 BEFORE INSERT 触发程序，或 1 个BEFORE UPDATE 触发程序和 1 个 AFTER UPDATE 触发程序。就是说 mysql 不支持oracle 中 create trigger

27、 * 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 V

29、 ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);ELSIF (:new.duty_id is null) THENaction := Delete;afterImg := ;INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTE

30、R_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);ELSEaction := Update;IF(befImg != afterImg) THENINSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category,

31、befImg, afterImg);END IF;END IF;EXCEPTION-_WHEN OTHERS thenbefImg := ;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 in

32、fo_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

33、_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);DECL

34、ARE 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 actio

35、n = 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 RO

36、WBEGINDECLARE 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

37、 = 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) THENset action = Delete;-_set afterImg = ;ELSEset action = Update;IF(befIm

38、g != afterImg) THENINSERT 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

39、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;-_e

40、xecute 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.

41、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

42、_UserIdORDER BY POSITION.PRIMARY_POSITION DESC;BEGINSELECT 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;ELSEOPEN c_PersonPosition;LOOPFETCH

43、 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) THENv_Result := CO

44、NCAT(CONCAT(v_Result, 兼), v_LevelName);END IF;END LOOP;CLOSE c_PersonPosition;IF LENGTH(v_Result)0 THENv_Result := CONCAT(v_Result, );END IF;IF SUBSTR(v_Result, LENGTH(v_Result)-1) = THENv_Result := SUBSTR(v_Result,1,LENGTH(v_Result)-2);END IF;END IF;-_RETURN v_Result;END;Mysql 下函数：CREATE FUNCTION G

45、ET_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 PE

46、RSON.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_I

47、D = v_UserIdORDER BY POSITION.PRIMARY_POSITION DESC;SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId;IF v_RecCount=0 THENSELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON WHERE USER_ID = v_UserId;-_ELSEOPEN c_PersonPosition;REPEATFET

48、CH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;IF NOT done THENset v_Result = IFNULL(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) THENset v_Result = CONCAT(CONCAT(v_Result, 兼), v_LevelName);END IF;END IF;UNTIL don

展开阅读全文