存储过程
drop procedure if exists pro_change_type ;
create procedure pro_change_type(table_name varchar(50))
begin
-- 修改实体表字段
-- 游标所使用变量需要在定义游标之前申明
declare column_name varchar(50);
-- 遍历数据结束标志 注意位置顺序
DECLARE done INT DEFAULT FALSE;
-- 注意用别名 因为id在上面已经有定义所以需要使用表的别名区别
declare cur_test CURSOR for select name from BusinessObjectColumns where BusinessObjectId=(select ID from BusinessObjects where `Name`=table_name) and Type='decimal';
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur_test;
repeat
fetch cur_test into column_name;
-- mysql动态执行sql
SET @alter_table_query = CONCAT('ALTER TABLE ',table_name,' MODIFY COLUMN `',column_name,'` VARCHAR(50)');
PREPARE alter_table_stmt FROM @alter_table_query;
EXECUTE alter_table_stmt;
DEALLOCATE PREPARE alter_table_stmt;
until done
end repeat;
-- 注意关闭游标
close cur_test;
-- 修改配置表字段
SET @update_query = CONCAT('update BusinessObjectColumns set Length=''50'',Type=''varchar'' where BusinessObjectId=(select ID from BusinessObjects where Name=?) and Type=''decimal''');
PREPARE update_stmt FROM @update_query;
set @table_name=table_name;
EXECUTE update_stmt USING @table_name;
DEALLOCATE PREPARE update_stmt;
end;
调用
call pro_change_type('mdmemploymenttype');