Oracle到MySQL的模式schema转换迁移conversation/migration

 

针对来自Oracle的不同的schema元素,是否存在自动转换到MYSQL的可能呢? 这里我们列出了相关的元素的转换情况。

SELECT:

 

子句 自动转换? 细节
WITH No 使用自定义存储过程来做准备数据,或重写查询以避免with子句
AS No
SELECT Yes
DISTINCT | UNIQUE | ALL Yes
select_list Yes
BULK COLLECT INTO No 用INTO替代
INTO Yes
record_name No
FROM Yes
@dblink No
materialized view No
TABLE (collection_expression) No
MODEL No MySQL 不支持MODEL
START WITH No MySQL 不支持树形查询,要用存储过程替代
CONNECT BY No MySQL 不支持树形查询,要用存储过程替代
PIVOT No
XML No
UNPIVOT No
WHERE Yes
GROUP BY Yes
CUBE No 要用存储过程替代
GROUPING SETS No 要用存储过程替代
HAVING Yes
ORDER BY Yes
SIBLINGS No
NULLS FIRST | NULLS LAST No MySQL 不支持 NULLS FIRST and NULLS LAST.  用order by 加case实现
FOR UPDATE Yes
OF No 要用 FOR UPDATE 替代 FOR UPDATE OF.
NOWAIT | WAIT No MySQL 不支持  WAIT and NOWAIT 子句. 要用FOR update就不能加nowait|wait
SKIP LOCKED No 用 FOR UPDATE without SKIP LOCKED替代
UNION Yes
INTERSECT | MINUS] Yes

 

INSERT

子句 自动转换? 细节
INTO table Yes
PARTITION Yes
PARTITION FOR No
SUBPARTITION No 要么插入数据到叠加分区,要么对INSERT做手动转换
VIEW No 用目标表替换INSERT语句涉及的VIEW。 若目标是视图上有INSTEAD OF触发器,解析并执行INSTEAD OF 触发器代码

 

MATERIALIZED VIEW No 做手动转换
subquery No 直接对底层面做操作
WITH table_collection_expression No
column … Yes
VALUES Yes
subquery Yes
RETURNING … INTO No .
LOG ERRORS No 可以在意外处理中加上对error记录插入到日志表的处理。

 

UPDATE

 

子句 自动转换? 细节
UPDATE [hint] Yes
table Yes
PARTITION Yes
PARTITION FOR No
SUBPARTITION No Either insert data into the overlying partition, or perform a manual transformation using the UPDATE statement.
VIEW No Perform an update on the underlying tables instead.
MATERIALIZED VIEW No
subquery No Perform this operation on the underlying tables instead.
WITH No
table_collection_expression Yes
SET Yes
VALUE
WHERE condition Yes
RETURNING … INTO No To perform this operation, divide the UPDATE statement with the RETURNING clause into an UPDATE statement with following INSERT statements that have the specified key conditions in the SELECT part.
LOG ERRORS No You can add error records by inserting them into the log in the exception block. Iterate through the errors in the exception block, add them to the log, and use the EXIT command when finished.

 

DELETE

Clause Automatically Converted Details
DELETE Yes
FROM Yes
PARTITION Yes
PARTITION FOR No Either insert data into the overlying partition, or perform a manual transformation using the DELETE statement.
SUBPARTITION No
VIEW No Perform a manual conversion.
MATERIALIZED VIEW No Perform a manual conversion.
subquery No Perform this operation on the underlying tables instead.
WITH No
table_collection_expression Yes
WHERE condition Yes
RETURNING … INTO No To perform this operation, divide the DELETE statement with the RETURNING clause into a DELETE statement with following INSERT statements and use the same key conditions in each SELECT.
LOG ERRORS No You can add error records by inserting them into the log in the exception block. Iterate through the errors in the exception block, add them to the log, and use the EXIT command when finished.

 

MERGE

 

语句 自动转换? 细节
MERGE No 用独立的INSERT,UPDATE,DELETE  语句来实现MERGE

 

 

TRUNCATE

子句 自动转换? 细节
TRUNCATE TABLE Yes
PRESERVE MATERIALIZED VIEW LOG No
PURGE MATERIALIZED VIEW LOG No
DROP STORAGE No
REUSE STORAGE No

 

锁表

子句 自动转换? 细节
PARTITION No
SUBPARTITION No
NOWAIT No

 

存储过程

元素 自动转换? 细节
LOCK TABLE No MYSQL在存储过程中不支持锁表
dbms_output.put_line No 将其表现修改为记录到日志表中, 可以使用 PD_ORACLE_EXT.PUT_LINE.
dbms_output.put No 将其表现修改为记录到日志表中, 可以使用 PD_ORACLE_EXT.PUT_LINE.

 

 

Clause Automatically Converted Details
GOTO No
FORALL No Try using a WHILE DO statement.
EXECUTE IMMEDIATE No
BULK COLLECT No
RETURNING BULK COLLECT INTO No
LABEL No Try rewriting variables without using labels.

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569