DROP PROCEDURE IF EXISTS `accounting`.`BATCH_AC_INV_ITEM_ADD_COLUMN`;
CREATE DEFINER=`root`@`%` PROCEDURE `BATCH_AC_INV_ITEM_ADD_COLUMN`()
BEGIN
declare i int ;
declare j int;
set i = 7;
while i < 13 do
set j = 0;
repeat
if i < 10 then
set @sql=concat('alter table ',CONCAT('ac_inv_item_2021',concat('0',i),'_',j),' add column `detail_id` varchar(120) null default null comment ',"'行明细唯一标识(BPM)'");
PREPARE stmt from @sql;
execute stmt;
else
set @sql=concat('alter table ',CONCAT('ac_inv_item_2021',i,'_',j),' add column `detail_id` varchar(120) null default null comment ',"'行明细唯一标识(BPM)'");
PREPARE stmt from @sql;
execute stmt;
end if;
set j = j + 1;
until j = 3 end repeat;
set i = i + 1;
end while;
set i = 1;
while i < 7 do
set j = 0;
repeat
if i < 10 then
set @sql=concat('alter table ',CONCAT('ac_inv_item_2022',concat('0',i),'_',j),' add column `detail_id` varchar(120) null default null comment ',"'行明细唯一标识(BPM)'");
PREPARE stmt from @sql;
execute stmt;
else
set @sql=concat('alter table ',CONCAT('ac_inv_item_2022',i,'_',j),' add column `detail_id` varchar(120) null default null comment ',"'行明细唯一标识(BPM)'");
PREPARE stmt from @sql;
execute stmt;
end if;
set j = j + 1;
until j = 3 end repeat;
set i = i + 1;
end while;
END;
mysql存储过程(批量增加表字段,针对分表)
最新推荐文章于 2023-05-03 10:59:53 发布