存储过程批量插入，添加字段

CREATE DEFINER=`root`@`%` PROCEDURE `batchInsert`(IN loop_times INT)
BEGIN  
     DECLARE var INT;   
     SET var = 0;  
set @exeSql=concat("insert into cdb_dynamic (typeId,addTime,userId) VALUES (1,1470540371,1)");  

     WHILE var < loop_times DO  
set @exeSql=concat(@exeSql,", (1,1470540371,1)");  
           SET var = var + 1;  
     END WHILE;  
     prepare stmt from @exeSql;  
     EXECUTE stmt;  
     deallocate prepare stmt;
     END;

 

CREATE DEFINER=`root`@`%` PROCEDURE `NewProcedure`()
BEGIN


DECLARE Done INT DEFAULT 0;

     DECLARE ShizuName VARCHAR(300);
/* 声明游标 */
     DECLARE rs CURSOR FOR  select table_name as tn from information_schema.tables where table_schema='stats' and table_name like  'cdb_click%';
/* 异常处理 */
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

/* 打开游标 */
      OPEN rs;

/* 逐个取出当前记录LingQi字段的值，需要进行最大值的判断 */
       FETCH NEXT FROM rs INTO ShizuName;
/* 遍历数据表 */
      REPEAT
            IF NOT Done THEN
-- insert into test (name) values  (ShizuName);

                   SET @STMT :=CONCAT("alter table `",ShizuName,"` add column addTime int(10) unsigned not null default 0;");   

                    PREPARE STMT FROM @STMT;   
                    EXECUTE STMT;   


                     SET @STMT :=CONCAT("alter table `",ShizuName,"` add column isDel tinyint(3) unsigned not null default 0;");   

                    PREPARE STMT FROM @STMT;   
                    EXECUTE STMT;   

/*UPDATE socialrelation SET LingQi = LingQi + 60 WHERE NodeName = ShizuName;*/
            END IF;

       FETCH NEXT FROM rs INTO ShizuName;

      UNTIL Done END REPEAT;

/* 关闭游标 */
      CLOSE rs;

END;