ORACLE|oracle利用函数实现oracle表生成mysql建表DDL python|MYSQL

一、应用背景
二、功能实现
三、参考资料

一、应用背景数据的迁移在工作中经常用到，尤其是不同类型的数据库建表语法不一致，下面通过oracle函数，输出mysql建表DDL。
二、功能实现 1、代码

create or replace function fn_crea_tab_oracle2mysql ( i_ownerin string, i_table_namein string, i_number_default_typein 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 Resultclob; cntnumber; data_typevarchar2(128); column_strvarchar2(4000); table_comments varchar2(4000); is_pk_columnnumber := 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 = '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.data_precision is not null and (c.data_scale = 0 or c.data_scale is null) ) then data_type := 'int(' || c.data_precision || ')'; 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; /* 自增主键：id列视为自增id列 */ if (c.column_name = upper(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 = upper(i_owner) and a.table_name = upper(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 = 'N' 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; /* 主键 */ 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 = upper(i_owner) and a.table_name = upper(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; /* 唯一键 */ 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 = upper(i_owner) and a.table_name = upper(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; /* 索引 */ 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 = upper(i_owner) and a.TABLE_NAME = upper(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) || ')'; /* 表注释 */ select max(a.COMMENTS) into table_comments from all_tab_comments a where owner = upper(i_owner) and table_name = upper(i_table_name); if (table_comments is not null) then Result := Result || 'COMMENT=''' || table_comments || ''''; end if; Result := Result || '; '; end if; return(Result); end fn_crea_tab_oracle2mysql;

2、效果