oracle转Mysql数据库 字段类型 INTEGER（orcle） 转化mysql变为 （decimal） 解决办法

我们需要把decimal 的列批量变为int型 ，那么一个mysql存储过程就可以搞定。

 

CREATE DEFINER=`zwb`@`%` PROCEDURE `batch_alter_tables_columntype`(
dbname varchar(50),
from_type varchar(32),
to_type varchar(32)
)
begin

declare tbl_name varchar(128);
declare parent_done int(1) default 0;

declare parent_cursor cursor for select table_name from information_schema.`TABLES` where table_type='BASE TABLE' and table_schema=dbname;
declare continue handler for not found set parent_done=1;

open parent_cursor;
parent_loop:loop fetch parent_cursor into tbl_name;
if parent_done=1 then leave parent_loop;
end if;

begin

declare col_name varchar(128);
declare son_done int(1) default 0;
declare sql_for_alter varchar(1024);
declare son_cursor cursor for select column_name from information_schema.`COLUMNS` where CONVERT(table_name using utf8) collate utf8_bin=convert(tbl_name using utf8) and convert(data_type using utf8) collate utf8_bin in (from_type) and table_schema=dbname;

declare continue handler for not found set son_done=1;

open son_cursor;
son_loop:loop fetch son_cursor into col_name;
if son_done=1 then leave son_loop;
end if;
set sql_for_alter=concat("alter table ",tbl_name," modify column ",col_name," ",to_type);
set @sql=sql_for_alter;
prepare stmt from @sql;
execute stmt;
set son_done=0;
end loop son_loop;
close son_cursor;

end;

set parent_done=0;
end loop parent_loop;
close parent_cursor;

end