需求:向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;