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

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;

 

转载于:https://www.cnblogs.com/zenghansen/p/5976700.html