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 <= 64 THENREPLACE (column_type,'FLOAT','decimal' ) WHEN column_type = 'TIMESTAMP(6)(11)' THEN'timestamp(6)' WHEN column_type LIKE '%CHAR%' THENREPLACE (REPLACE (column_type,'NVARCHAR2','VARCHAR' ),'VARCHAR2','VARCHAR' ) WHEN column_type = 'NUMBER(22)' THEN'bigint' WHEN (column_type LIKE '%NUMBER%,0%' OR 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;