Oracle表结构转Mysql表结构

1. fnc_table_to_mysql 主体程序

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  
  作者：叶正盛 2013-07-27  
  新浪微博:@yzsind-叶正盛  
  参数说明：  
  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;  
/

2 需要转换的oracle 表

CREATE TABLE  "TEST_MYSQL"."UC_CUST_SY_CPZX" 
   (	"BUSI_DATE" NUMBER(*,0), 
	"TRADE_DATE" NUMBER(*,0), 
	"YEAR_ID" NUMBER(*,0), 
	"MONTH_ID" NUMBER(*,0), 
	"DAY_ID" NUMBER(*,0), 
	"CPLX" CHAR(2 BYTE), 
	"CPDM" VARCHAR2(100 BYTE), 
	"PRODUCT_NAME" VARCHAR2(300 BYTE), 
	"DWJZ" NUMBER(19,4) NOT NULL ENABLE, 
	"LJJZ" NUMBER(19,4) NOT NULL ENABLE, 
	"RZZL" NUMBER(19,4) NOT NULL ENABLE, 
	"FQJZ" NUMBER(19,4) NOT NULL ENABLE, 
	"LJJZSYL" NUMBER(19,4) NOT NULL ENABLE, 
	"CPGLMS" NUMBER(*,0)
   );

3 转换

SQL> select dbms_lob.substr(fnc_table_to_mysql('TEST_MYSQL','UC_CUST_SY_CPZX','decimal','ID')) FROM DUAL;
CREATE TABLE `uc_cust_sy_cpzx`(
  `busi_date` decimal,
  `trade_date` decimal,
  `year_id` decimal,
  `month_id` decimal,
  `day_id` decimal,
  `cplx` char(2),
  `cpdm` varchar(100),
  `product_name` varchar(300),
  `dwjz` decimal(19,4),
  `ljjz` decimal(19,4),
  `rzzl` decimal(19,4),
  `fqjz` decimal(19,4),
  `ljjzsyl` decimal(19,4),
  `cpglms` decimal
);