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

锁表

存储过程

元素	自动转换？	细节
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.

Comments