createor replace function fnc_table_to_mysql
( i_owner in string,
i_table_name in string,
i_number_default_type in string :='decimal',
i_auto_incretment_column_name in string :='%ID'
)
/*
功能:ORACLE表生成MYSQL建表DDL
作者:叶正盛 2013-07-27
新浪微博:@yzsind-叶正盛
参数说明:
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 是根据字段名规则加一些判断,设置不一定准确,需检查
*/returnclobisResultclob;
cnt number;
data_type varchar2(128);
column_str varchar2(4000);
pk_str varchar2(4000);
table_comments varchar2(4000);
is_pk_column number :=0;
beginselectcount(*)
into cnt
from all_tables
where owner = i_owner
and table_name = i_table_name;
if (cnt =0) then
RAISE_APPLICATION_ERROR(-20000,'can''t found table,please check input!');
elseResult :='CREATE TABLE `'||lower(i_table_name) ||'`(';
--column 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 = i_owner
and a.table_name = 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
orderby a.column_id) loop
if (c.data_type ='VARCHAR2'or c.data_type ='NVARCHAR2') then
data_type :='varchar('|| c.data_length ||')';
elsif (c.data_type ='CHAR'or c.data_type ='NCHAR') then
data_type :='char('|| c.data_length ||')';
elsif (c.data_type ='NUMBER') then
if (c.column_name like'%ID'and c.data_scale isnull) then
data_type :='bigint';
elsif (c.data_precision<3and c.data_scale =0) then
data_type :='tinyint';
elsif (c.data_precision<5and c.data_scale =0) then
data_type :='smallint';
elsif (c.data_precision<10and c.data_scale =0) then
data_type :='int';
elsif (c.data_precision isnotnulland c.data_scale =0) then
data_type :='bigint';
elsif (c.data_precision isnotnulland c.data_scale isnotnull) then
data_type :='decimal('|| c.data_precision ||','||
c.data_scale ||')';
else
data_type := i_number_default_type;
end if;
elsif (c.data_type ='DATE'or c.data_type like'TIMESTAMP%') then
data_type :='datetime';
elsif (c.data_type ='CLOB'or c.data_type ='NCLOB'or
c.data_type ='LONG') then
data_type :='text';
elsif (c.data_type ='BLOB'or c.data_type ='LONG RAW') then
data_type :='blob';
elsif (c.data_type ='BINARY_FLOAT') then
data_type :='float';
elsif (c.data_type ='BINARY_DOUBLE') then
data_type :='double';
else
data_type := c.data_type;
end if;
column_str :=' `'||lower(c.column_name) ||'` '|| data_type;
if (c.column_name like i_auto_incretment_column_name and
(c.data_scale isnullor c.data_scale =0)) thenselectcount(*)
into is_pk_column
from all_constraints a, all_cons_columns b
where a.owner = i_owner
and a.table_name = i_table_name
and a.constraint_type ='P'and a.OWNER = b.OWNER
and a.TABLE_NAME = b.TABLE_NAME
and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and b.COLUMN_NAME = c.column_name;
if is_pk_column >0then
column_str := column_str ||' AUTO_INCREMENT';
end if;
end if;
if c.nullable ='NO'then
column_str := column_str ||' NOT NULL';
end if;
if (trim(c.data_default) isnotnull) then
column_str := column_str ||' DEFAULT '||trim(replace(replace(c.data_default, chr(13), ''),
chr(10),
''));
end if;
if c.comments isnotnullthen
column_str := column_str ||' COMMENT '''|| c.comments ||'''';
end if;
Result :=Result|| chr(10) || column_str ||',';
end loop;
--pk for c in (select a.constraint_name, wm_concat(a.column_name) pk_columns
from (select a.CONSTRAINT_NAME,
'`'|| b.COLUMN_NAME ||'`' column_name
from all_constraints a, all_cons_columns b
where a.owner = i_owner
and a.table_name = i_table_name
and a.constraint_type ='P'and a.OWNER = b.OWNER
and a.TABLE_NAME = b.TABLE_NAME
and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
orderby b.POSITION) a
groupby a.constraint_name) loop
Result :=Result|| chr(10) ||' PRIMARY KEY ('||lower(c.pk_columns) ||'),';
end loop;
--unique for c in (select a.constraint_name, wm_concat(a.column_name) uk_columns
from (select a.CONSTRAINT_NAME,
'`'|| b.COLUMN_NAME ||'`' column_name
from all_constraints a, all_cons_columns b
where a.owner = i_owner
and a.table_name = i_table_name
and a.constraint_type ='U'and a.OWNER = b.OWNER
and a.TABLE_NAME = b.TABLE_NAME
and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
orderby b.POSITION) a
groupby a.constraint_name) loop
Result :=Result|| chr(10) ||' UNIQUE KEY `'||lower(c.constraint_name) ||'`('||lower(c.uk_columns) ||'),';
end loop;
-- index for c in (select a.index_name, wm_concat(a.column_name) ind_columns
from (select a.index_name,
'`'|| a.COLUMN_NAME ||'`' column_name
from all_ind_columns a
where a.table_owner = i_owner
and a.TABLE_NAME = i_table_name
andnotexists
(select index_name
from all_constraints b
where a.TABLE_OWNER = b.owner
and a.TABLE_NAME = b.TABLE_NAME
and a.INDEX_NAME = b.INDEX_NAME)
orderby a.COLUMN_POSITION) a
groupby a.index_name) loop
Result :=Result|| chr(10) ||' KEY `'||lower(c.index_name) ||'`('||lower(c.ind_columns) ||'),';
end loop;
Result := substr(Result, 1, length(result) -1) || chr(10) ||')';
--table comments selectmax(a.COMMENTS)
into table_comments
from all_tab_comments a
where owner = i_owner
and table_name = i_table_name;
if (table_comments isnotnull) thenResult :=Result||'COMMENT='''|| table_comments ||'''';
end if;
Result :=Result||';';
end if;
return(Result);
end fnc_table_to_mysql;
/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库