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 ;
