mysql数据库存储过程游标循环，提前退出

andy27367451

　　需求：向trade这个数据库中的每一个表增加多个字段
　　遇到问题：存储过程，游标，循环，动态sql执行
　　注意：
　　mysql存储过程在我所使用的5.5版本中不能使用 show 的命令，利用 information_schema 获得表的相关信息。
　　游标循环中出现 select into 赋值 为 null 的时候，循环会 提前退出，解决方法有三种

• 不用select into
  
• select aa into bb，aa改为count（aa），之后的代码由判断null改为判断0
  
• 当赋值结果为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;