存储过程批量改表结构

CREATE DEFINER=`wms2testopr`@`%` PROCEDURE `material_moving_test`.`alter_table_structure_procedure`(_table_name VARCHAR(100), operTpe VARCHAR(100), colum varchar(100), fieldType varchar(64),comment varchar(100))

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE vtable_name varchar(100);

DECLARE cur CURSOR for

SELECT table_name

FROM information_schema.tables

WHERE TABLE_name like concat(_table_name, '%');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP

FETCH cur INTO vtable_name;

IF done THEN

LEAVE read_loop;

END IF;

-- 执行修改表结构的操作

-- ...

SET @alter_stmt =CONCAT('ALTER TABLE ', vtable_name, ' ', operTpe, ' ', colum, ' ', fieldType, ' ', 'default null comment \'', comment, ' ', '\'');

PREPARE stmt FROM @alter_stmt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END LOOP;

CLOSE cur;

end