一、应用背景
数据的迁移在工作中经常用到,尤其是不同类型的数据库建表语法不一致,下面通过oracle函数,输出mysql建表DDL。
二、功能实现
1、代码
create or replace function fn_crea_tab_oracle2mysql
( i_owner in string,
i_table_name in string,
i_number_default_type in string := 'decimal',
i_auto_incretment_column_name in string := 'ID' -- '%ID'
)
/*
功能:ORACLE表生成MYSQL建表DDL
参数说明:
i_owner:schema名
i_table_name:表名
i_number_default_type:NUMBER默认转换的类型,缺省是decimal
i_auto_incretment_column_name:自增属性字段名称规则,默认是ID,可传进来已知的自增字段
已知问题:
1.不支持分区
2.不支持函数索引,位图索引等特殊索引定义
3.不支持自定义数据类型,不支持ROWID,RAW等特殊数据类型
4.不支持外键
5.不支持自定义约束
6.不支持与空间、事务相关属性
7.DATE与TIMESTAMP转换成datetime,需注意精度
8.超大NUMBER直接转换为bigint,需注意精度
9.auto incretment 是根据字段名规则加一些判断,设置不一定准确,需检查
*/
return clob is
Result clob;
cnt number;
data_type varchar2(128);
column_str varchar2(4000);
table_comments varchar2(4000);
is_pk_column number := 0;
begin
select count(*) into cnt
from all_tables
where owner = upper(i_owner)
and table_name = upper(i_table_name);
if (cnt = 0) then
RAISE_APPLICATION_ERROR(-20000,'can not found table,please check input!');
else
Result := 'CREATE TABLE `' || lower(i_table_name) || '`(';
/*
字段数据类型
*/
for c in (select a.column_name,
a.data_type,
a.data_length,
a.data_precision,
a.data_scale,
a.nullable,
a.data_default,
b.COMMENTS
from all_tab_cols a, all_col_comments b
where a.owner = upper(i_owner)
and a.table_name = upper(i_table_name)
and a.HIDDEN_COLUMN = 'NO'
and a.owner = b.OWNER
and a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
order by a.column_id) loop
if (c.data_type = 'VARCHAR2' or c.data_type =