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

当前位置:我的异常网» 数据库 » mysql的存储过程-数据库的复制

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

www.myexceptions.net  网友分享于：2013-03-18  浏览：0次

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 ;

文章评论