MySQL存储过程：内部调用存储过程、存储过程实现遍历数据库建表以及修改字段

 转载： https://blog.csdn.net/weixin_42159024/article/details/81488788

 

 实现功能： 

升级数据库。通过遍历所有数据库，对每个数据库进行升级，通过新建表、字段升级。这里主要通过数据库名.表名来实现在同一个存储过程中切换不同数据库。

 

drop procedure if exists addColumn;
drop procedure if exists updateDataBase;
delimiter $$;
--创建存储过程：新增字段，传入参数：数据库名、表名、字段名、字段类型（int、VARCHAR...）
create procedure 'addColumn'(in dataName VARCHAR(100),in tableName VARCHAR(100),in columnName VARCHAR(100),in columnType VARCHAR(100))
BEGIN
	DECLARE column_num int;
	set @sqlstr=concat("select count(*) into @column_num from information_schema.columns where table_schema='",dataName,"' and table_name='",tableName,"' and column_name='",columnName,"'");
	PREPARE sqlstr FROM @sqlstr;
	EXECUTE sqlstr;
	if @column_num=0 then
	set @sqlstr=concat("alter table ",dataName,".",tableName," add column ",columnName,columnType);
	PREPARE sqlstr FROM @sqlstr;
	EXECUTE sqlstr;
    end if;
END
$$
delimiter$$;

--创建存储过程：更新数据库，通过创建表及字段来升级数据库
drop procedure if exists updateDataBase;
delimiter$$;
create procedure 'updateDataBase'()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE dataName VARCHAR(100);
    DECLARE s int DEFAULT 0;
    --将以ticket_开头的数据库遍历
    DECLARE consume CURSOR FOR select distinct table_schema as dataName from information_schema.'TABLES' where table_schema like 'ticket_%';
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
    OPEN consume;
        FETCH consume into dataName;
        while s<>1 DO
            call addColumn(dataName,'tbl_dc_neinfo','isPassed',' varchar(128) DEFAULT NULL');--调用存储过程，在表tbl_dc_neinfo中新建字段isPassed varchar(128) DEFAULT NULL

            --新建表，在不同的数据库中
            set @sql_create_table=concat(
            "CREATE TABLE IF NOT EXISTS ",dataName,"tbl_dc_neinfo",
            "(
                'OID' bigint(20) NOT NULL AUTO_INCREMENT,
                '字段2' 类型,
                PRIMARY KEY('OID')
             )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
            PREPARE sql_create_table FROM @sql_create_table;
            EXECUTE sql_create_table;

            FETCH consume INTO dataName;
        end WHILE;
    CLOSE consume;
END
$$;
delimiter$$;
call updateDataBase();
drop procedure if exists updateDataBase;
drop procedure if exists addColumn;