ORACLE表生成MYSQL建表DDL

曾经终败给现在 2024-02-19 18:17 38阅读 0赞

create or 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  
参数说明：  
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);  
pk\_str varchar2(4000);  
table\_comments varchar2(4000);  
is\_pk\_column number := 0;  
begin  
select count(\*)  
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!');  
else  
Result := '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  
order by 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 is null) then  
data\_type := 'bigint';  
elsif (c.data\_precision<3 and c.data\_scale = 0) then  
data\_type := 'tinyint';  
elsif (c.data\_precision<5 and c.data\_scale = 0) then  
data\_type := 'smallint';  
elsif (c.data\_precision<10 and c.data\_scale = 0) then  
data\_type := 'int';  
elsif (c.data\_precision is not null and c.data\_scale = 0) then  
data\_type := 'bigint';  
elsif (c.data\_precision is not null and c.data\_scale is not null) 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 is null or c.data\_scale = 0)) then  
select count(\*)  
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 > 0 then  
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) is not null) then  
column\_str := column\_str || ' DEFAULT ' ||  
trim(replace(replace(c.data\_default, chr(13), ''),  
chr(10),  
''));  
end if;  
if c.comments is not null then  
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  
order by b.POSITION) a  
group by 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  
order by b.POSITION) a  
group by 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  
and not exists  
(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)  
order by a.COLUMN\_POSITION) a  
group by 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  
select max(a.COMMENTS)  
into table\_comments  
from all\_tab\_comments a  
where owner = i\_owner  
and table\_name = i\_table\_name;  
if (table\_comments is not null) then  
Result := Result || 'COMMENT=''' || table\_comments || '''';  
end if;  
Result := Result || ';';  
end if;  
return(Result);  
end fnc\_table\_to\_mysql;  
/