mysql数据库存储过程游标循环，提前退出 博客分类： mysql 存储过程游标循环失败动态sql增加字段

需求：向trade这个数据库中的每一个表增加多个字段

遇到问题：存储过程，游标，循环，动态sql执行

注意：

mysql存储过程在我所使用的5.5版本中不能使用 show 的命令，利用 information_schema 获得表的相关信息。

游标循环中出现 select into 赋值 为 null 的时候，循环会 提前退出，解决方法有三种

1. 不用select into
2. select aa into bb，aa改为count（aa），之后的代码由判断null改为判断0
3. 当赋值结果为null的时候，会自动修改循环触底的标志，所以手动在循环底部增加  set b = 1；

-- Procedure "useCursor" DDL
drop PROCEDURE if exists  useCursor;
CREATE PROCEDURE `useCursor`()
BEGIN
    /*局部变量的定义 declare*/
	
  declare temp_table_name varchar(100) default '';
  
  declare temp varchar(100);
  
  declare b int default 0;    /*是否达到记录的末尾控制变量*/
  
  declare cur CURSOR FOR (SELECT table_name from information_schema.`TABLES` where TABLE_SCHEMA = 'trade');
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
  
  /*开游标*/
    OPEN cur;
    /*游标向下走一步，将查询出来的两个值付给定义的两个变量*/
    FETCH cur INTO temp_table_name;
    WHILE ( b<>1 ) DO
	#1
	SELECT COLUMN_NAME into temp from information_schema.`COLUMNS` where TABLE_SCHEMA = 'trade' and table_name = temp_table_name and COLUMN_NAME = 'bb';
	if temp is null then
		set @sql = concat('alter table ', temp_table_name);
		set @sql = concat(@sql, ' add bb varchar(50);' );
		
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	end if;
	
	#2
	SELECT COLUMN_NAME into temp from information_schema.`COLUMNS` where TABLE_SCHEMA = 'trade' and table_name = temp_table_name and COLUMN_NAME = 'ww';
	if temp is null then
		set @sql = concat('alter table ', temp_table_name);
		set @sql = concat(@sql, ' add ww varchar(50);' );
		
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	end if;
	
	#3
	SELECT COLUMN_NAME into temp from information_schema.`COLUMNS` where TABLE_SCHEMA = 'trade' and table_name = temp_table_name and COLUMN_NAME = 'ff';
	if temp is null then
		set @sql = concat('alter table ', temp_table_name);
		set @sql = concat(@sql, ' add ff varchar(50);' );
		
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	end if;
	
	#4
	SELECT COLUMN_NAME into temp from information_schema.`COLUMNS` where TABLE_SCHEMA = 'trade' and table_name = temp_table_name and COLUMN_NAME = 'pp';
	if temp is null then
		set @sql = concat('alter table ', temp_table_name);
		set @sql = concat(@sql, ' add pp varchar(50);' );
		
		PREPARE STMT FROM @sql;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
	end if;
	
    /*游标向下走一步*/
	set b = 0;
    FETCH cur INTO temp_table_name;
    END WHILE;
    CLOSE cur;
END;
call useCursor;
drop PROCEDURE if exists  useCursor;