-- 给customer表添加upload_date字段,将customer_nbr字段的长度改为20，同时将创建时间赋值给导入时间
DELIMITER //
drop procedure if exists customerAddField//
CREATE PROCEDURE customerAddField()
BEGIN
DECLARE tableName varchar(100) 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_customer%' and table_name != 'tb_ucpaas_customer_contact' and table_name != 'tb_ucpaas_customer_contact_record';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN taskCursor;
REPEAT
FETCH taskCursor INTO tableName;
IF not done THEN
set @sql2=concat('update ',tableName,' set upload_date=create_date');
PREPARE stmt from @sql2;
execute stmt;
set @sql3=concat('ALTER TABLE ',tableName,' MODIFY customer_nbr varchar(20) DEFAULT NULL ');
PREPARE stmt from @sql3;
execute stmt;
set @sql4=concat('ALTER TABLE ',tableName,' ADD INDEX idx_customer_nbr( customer_nbr ) ');
PREPARE stmt from @sql4;
execute stmt;
END IF;
UNTIL done END REPEAT;
CLOSE taskCursor;
END
//
DELIMITER;
CALL customerAddField();
