mysql存储过程——用于数据库的备份与还原

DELIMITER $$ USE `bjcmdb`$$ /* 用于数据库备份的存储过程 *@parameter：databaseName,数据库的名称 *@author：张红斌 */ DROP PROCEDURE IF EXISTS `pro_database_backup`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_database_backup`(IN databaseName CHAR(40)) BEGIN ##定义变量 DECLARE tableName VARCHAR(40); DECLARE oldtableName VARCHAR(40); DECLARE newtableName VARCHAR(40); DECLARE cur_stop INT DEFAULT 0; ##首先这里对游标进行定义 DECLARE cur_tableName CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'bjcmdb' AND TABLE_NAME NOT IN ('c_basisline'); ##这个是个条件处理,针对SQLSTATE '02000'的条件 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_stop = NULL; ##创建数据库 SET @createDatabaseSql :=CONCAT("create database ",databaseName,";"); PREPARE STMT FROM @createDatabaseSql; EXECUTE STMT; ## 格式化数据库名称 SET databaseName=CONCAT(databaseName,"."); ## 开启游标 OPEN cur_tableName; /*游标向下走一步，将查询出来的值付给定义的变量*/ FETCH cur_tableName INTO tableName; ##直到查询结果为空时结束遍历 WHILE ( cur_stop IS NOT NULL) DO ##赋表名 SET oldtableName=CONCAT('bjcmdb.',tableName); SET newtableName=CONCAT(databaseName,tableName); ##创建表，包括表结构和索引 SET @createsql :=CONCAT("CREATE TABLE ",newtableName," LIKE ",oldtableName,";"); PREPARE STMT FROM @createsql; EXECUTE STMT; ##复制数据到指定库的表 SET @insertsql :=CONCAT("INSERT INTO ",newtableName," SELECT * FROM ",oldtableName,";"); ##SELECT @insertsql; PREPARE STMT FROM @insertsql; EXECUTE STMT; FETCH cur_tableName INTO tableName; END WHILE; CLOSE cur_tableName; END$$ DELIMITER ;