系统开发完毕后，需要完备测试系统页面的逻辑功能，一条条考虑各种情况往数据库里插数据太麻烦了，为此我写了一个存储过程批量造数据，例子如下，共有三张表wideip，pool，status表
1.其中一个wideip包含多个pool(一对多)
2.status表是一个字典表有初始数据，wideip,pool里都有status_id外键，对应于status表里的id。
初始值为
INSERT INTO `status` VALUES ('0', 'available');
INSERT INTO `status` VALUES ('1', 'offline');
INSERT INTO `status` VALUES ('2', 'unknown');
INSERT INTO `status` VALUES ('3', 'unavilable');
3.pool里的wideip_id是一个外键关联了wideip的id
4.enable标识wideip和pool的开关，只有0,1两个状态
对于wideip来说，enable(2种变化)和status_id(4种变化)属性是可变的，因此完备的情况有2x4种
对于某个wideip下面的pool来说，enable(2种变化)和status_id(4种变化)属性是可变的，因此完备的情况有2x4种xwideip的变化=2x4x2x4=64种
造数据的过程如下
1.先清空wideip和pool表，恢复自增为1
2.每插一个wideip,针对这个wideip造出8个完备的pool记录
表结构代码：
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for pool
-- ----------------------------
CREATE TABLE `pool` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`wideip_id` int(11) NOT NULL,
`enable` tinyint(4) NOT NULL,
`status_id` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `wideip_pool_fk` (`wideip_id`),
KEY `status_pool_fk` (`status_id`),
CONSTRAINT `status_pool_fk` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
CONSTRAINT `wideip_pool_fk` FOREIGN KEY (`wideip_id`) REFERENCES `wideip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for status
-- ----------------------------
CREATE TABLE `status` (
`id` tinyint(4) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Table structure for wideip
-- ----------------------------
CREATE TABLE `wideip` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`enable` tinyint(4) NOT NULL,
`status_id` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `status_id` (`status_id`),
CONSTRAINT `wideip_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `status` VALUES ('0', 'available');
INSERT INTO `status` VALUES ('1', 'offline');
INSERT INTO `status` VALUES ('2', 'unknown');
INSERT INTO `status` VALUES ('3', 'unavilable');
造数据的存储过程
/*清空所有数据数据*/
drop procedure if exists clear_all_data;
delimiter ;;
create procedure clear_all_data()
BEGIN
/*清空数据*/
delete from wideip;
/*让自增从1*/
alter table wideip auto_increment 1;
/*清空pool数据*/
delete from pool;
/*让pool自增从1*/
alter table pool auto_increment 1;
END;;
delimiter ;
drop procedure if exists make_pool_data;
/*造每个wideip对于的pool数据*/
delimiter ;;
create procedure make_pool_data(wideipId int)
BEGIN
declare d_enable int default 0;
declare d_status_id int default 0;
declare isError int default 0;
declare Done int default 0;
declare i int default 0;
/* 声明游标 */
DECLARE rs CURSOR FOR select id from status order by id;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;
REPEAT
/* 打开游标 */
OPEN rs;
REPEAT
/*取status_id*/
FETCH rs INTO d_status_id;
IF Done!=1 THEN
select d_enable,d_status_id;
set i=i+1;
insert into pool(name,enable,status_id,wideip_id) values(concat("name",wideipId,"_",i),d_enable,d_status_id,wideipId);
END IF;
UNTIL d_status_id is NULL or Done=1 END REPEAT;
CLOSE rs;
set d_enable=d_enable+1;
set Done=0;
UNTIL d_enable >= 2 END REPEAT;
END;;
delimiter ;
drop procedure if exists make_wideip_data;
/*造wideip的数据并调用 make_pool_data */
delimiter ;;
create procedure make_wideip_data()
BEGIN
declare d_enable int default 0;
declare d_status_id int default 0;
declare isError int default 0;
declare Done int default 0;
declare i int default 0;
declare newWideipId int default 0;
/* 声明游标 */
DECLARE rs CURSOR FOR select id from status order by id;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;
REPEAT
/* 打开游标 */
OPEN rs;
REPEAT
/*取status_id*/
FETCH rs INTO d_status_id;
IF Done!=1 THEN
select d_enable,d_status_id;
set i=i+1;
insert into wideip(name,enable,status_id) values(concat("name",i),d_enable,d_status_id);
set newWideipId=last_insert_id();
call make_pool_data(newWideipId);
END IF;
UNTIL d_status_id is NULL or Done=1 END REPEAT;
CLOSE rs;
set d_enable=d_enable+1;
set Done=0;
UNTIL d_enable >= 2 END REPEAT;
END;;
delimiter ;
©著作权归作者所有：来自51CTO博客作者yifangyou的原创作品，如需转载，请注明出处，否则将追究法律责任
mysql测试存储过程数据库
共同学习，写下你的评论
评论加载中...
作者其他优质文章
