Oracle转Mysql建表脚本

–Oracle转mysql建表脚本
SELECT
t.column_id,
‘edi_’ || t.table_name AS table_name,
CASE

WHEN t1.table_name IS NOT NULL THENREPLACE (t.ddl_sql,\',\',\');\' ) ELSE t.ddl_sql 

END AS ddl_sql
FROM
(–拼接建表语句
SELECT
column_id,
table_name,
lower(
CASE

WHEN nullable = \'N\' THENcolumn_name || \' \' || column_type_new || \' NOT NULL COMMENT \' || \'\'\'\' || column_comments || \'\'\',\' ELSE column_name || \' \' || column_type_new || \' COMMENT \' || \'\'\'\' || column_comments || \'\'\',\' END ) AS ddl_sql FROM(---Oracle转mysqlSELECTlower(CASEWHEN column_type in(\'VARCHAR2(2000)\',\'VARCHAR2(4000)\') THEN\'text\' WHEN column_type LIKE \'%FLOAT%\' AND data_precision > 64 THEN\'decimal(64)\' WHEN column_type LIKE \'%FLOAT%\' AND data_precision  10 THENREPLACE (REPLACE (column_type,\'NUMBER\',\'bigint\' ),\',0\',\'\' ) WHEN column_type LIKE \'%NUMBER%,0%\' AND data_precision <= 10 THENREPLACE (REPLACE (column_type,\'NUMBER\',\'int\' ),\',0\',\'\' ) WHEN column_type LIKE \'%NUMBER%\' AND column_type NOT LIKE \'%NUMBER,0%\' THENREPLACE (column_type,\'NUMBER\',\'decimal\' ) WHEN column_type = \'DATE(7)\' THEN\'datetime(0)\' ELSE column_type END ) AS column_type_new,column_id,table_name,table_comments,column_name,column_comments,data_type,data_length,data_precision,data_scale,nullable FROM(--oracle字段拼接SELECTt2.column_id,t.table_name,t1.comments AS table_comments,t2.column_name,REPLACE (t3.comments,\'\'\'\',\'\' ) AS column_comments,data_type || \'(\' ||CASE--number类型特殊处理WHEN data_type NOT IN (\'NUMBER\',\'FLOAT\' ) THENDATA_LENGTH || (CASEWHEN data_precision IS NOT NULL THEN\',\' || data_precision || \')\' ELSE \')\' END ) ELSECASEWHEN data_precision IS NOT NULL THENdata_precision || (CASEWHEN data_scale IS NOT NULL THEN\',\' || data_scale || \')\' ELSE \')\' END ) ELSE DATA_LENGTH || (CASEWHEN data_precision IS NOT NULL THEN\',\' || data_precision || \')\' ELSE \')\' END ) END END AS column_type,data_type,data_length,data_precision,data_scale,nullable FROMuser_tables tINNER JOIN user_tab_comments t1 ON t.table_name = t1.table_nameINNER JOIN user_tab_columns t2 ON t.table_name = t2.table_nameINNER JOIN user_col_comments t3 ON t.table_name = t3.table_name AND t2.column_name = t3.column_name WHEREt.table_name IN (\'TEST\') ) n ) m UNION ALLSELECT0 AS column_id,table_name,lower(\'CREATE TABLE edi_\' || table_name || \'(\' ) AS ddl_sql FROMuser_tables WHEREtable_name IN (\'TEST\') ) tLEFT JOIN (SELECTtable_name,max(column_id ) AS column_id FROMuser_tab_columns GROUP BYtable_name ) t1 ON t.table_name = t1.table_name AND t.column_id = t1.column_id ORDER BYt.table_name,t.column_id;

–索引
SELECT
m.index_name,
lower(
CASE

WHEN m.uniqueness = \'UNIQUE\' THEN\'CREATE UNIQUE INDEX \' || m.index_name || \' ON edi_\' || m.table_name || \'(\' || m.column_name || \')\' ELSE \'CREATE INDEX \' || m.index_name || \' ON edi_\' || m.table_name || \'(\' || m.column_name || \')\' END ) || \';\' AS create_index FROM(SELECTt.index_name,t.table_name,t.uniqueness,wm_concat (t1.column_name ) AS column_name FROMuser_indexes tINNER JOIN user_ind_columns t1 ON t.table_name = t1.table_name AND t.index_name = t1.index_name WHEREt.table_name IN (\'TEST\') GROUP BYt.index_name,t.table_name,t.uniqueness ) m;