Oracle Table Generation MySQL table creation DDL

Oracle Table Generation MySQL table creation DDL

Some colleagues asked if there were any small tools for converting Oracle table to MySQL syntax, so they wrote this small function and made it public. Maybe someone could use it.

 

Create or replace function fnc_table_to_mysql (I _owner in string, I _table_name in string, I _number_default_type in string: = 'decimal', role in string: = '% id')/* function: oracle Table Generation MySQL table creation DDL Author: ye Zhengsheng 2013-07-27 Sina Weibo: @ yzsind-ye Zhengsheng parameter description: I _owner: schema name I _table_name: Table Name I _number_default_type: Number default conversion type, the default value is decimal I _auto_incretment_column_name: auto-increment attribute field name rule. The default value is % ID. Known issues: 1. partition is not supported. function cable is not supported. Definition of special indexes such as Bitmap indexes. custom data types are not supported, and special data types such as rowid and raw are not supported. foreign key 5 is not supported. custom constraints are not supported. 6. properties related to space and transactions are not supported. to convert date and timestamp to datetime, you must note that the precision is 8. super Large Number is directly converted to bigint, so the precision is 9. auto incretment makes some judgments based on field name rules. The settings are not necessarily accurate. Check */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 Al Rochelle 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. column_name,. data_type,. data_length,. data_precision,. data_scale,. nullable,. data_default, B. comments from all_tab_cols A, all_col_comments B where. owner = I _owner and. table_name = I _table_name and. hidden_column = 'no' and. owner = B. owner and. table_name = B. table_name and. column_name = B. column_name order by. column_id) loop if (C. data_type = 'varchar2' or C. data_type = 'varchar2') 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 = 'null' 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. owner = I _owner and. table_name = I _table_name and. constraint_type = 'p' and. owner = B. owner and. table_name = B. table_name and. 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; 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 (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. constraint_name, wm_concat (. column_name) pk_columns from (select. constraint_name, ''' | B. column_name | ''' column_name from all_constraints A, all_cons_columns B where. owner = I _owner and. table_name = I _table_name and. constraint_type = 'p' and. owner = B. owner and. table_name = B. table_name and. constraint_name = B. constraint_name order by B. position) a group by. constraint_name) loop result: = Result | CHR (10) | 'Primary key ('| Lower (C. pk_columns) | '),'; end loop; -- Unique for C in (select. constraint_name, wm_concat (. column_name) uk_columns from (select. constraint_name, ''' | B. column_name | ''' column_name from all_constraints A, all_cons_columns B where. owner = I _owner and. table_name = I _table_name and. constraint_type = 'U' and. owner = B. owner and. table_name = B. table_name and. constraint_name = B. constraint_name order by B. position) a group by. constraint_name) loop result: = Result | CHR (10) | 'unique key'' | Lower (C. constraint_name) | ''('| Lower (C. uk_columns) | '),'; end loop; -- index for C in (select. index_name, wm_concat (. column_name) ind_columns from (select. index_name, ''' |. column_name | ''' column_name from all_ind_columns A where. table_owner = I _owner and. table_name = I _table_name and not exists (select index_name from all_constraints B where. table_owner = B. owner and. table_name = B. table_name and. index_name = B. index_name) order by. column_position) a group by. 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 (. 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 ;/

Source Sina micro disk: http://vdisk.weibo.com/s/Eco-DT0ZVUa