mysql存储过程repeat循环给多个表名相似的表添加相同字段
程序员文章站
2024-03-22 10:38:10
...
-- 给task表及子表添加客户类型字段
DELIMITER // --结束符号定义为//
drop procedure if exists taskAddFieldPro//
CREATE PROCEDURE taskAddFieldPro()
BEGIN
DECLARE tableName varchar(50) default '';
DECLARE done INT DEFAULT 0;
DECLARE taskCursor CURSOR FOR select table_name from information_schema.tables where table_schema='uc_ipcc_cloud_ai' and table_name like 'tb_ucpaas_task_run%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN taskCursor;
REPEAT
FETCH taskCursor INTO tableName;
IF not done THEN
set @sql=concat('alter table ',tableName,' add cust_type char(1) DEFAULT NULL COMMENT "A/B/C/D/E/F"');
PREPARE stmt from @sql;
execute stmt;
END IF;
UNTIL done END REPEAT;
CLOSE taskCursor;
END
//
DELIMITER; -- 重新定义为分号
CALL taskAddFieldPro();
上一篇: &和&& |和|| 的区别
下一篇: 避免成员函数的参数名与成员变量名相同