mysql的存储过程---数据库的复制

DELIMITER $$

USE `bjcmdb`$$

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 ;