Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4165836
  • 博文数量: 291
  • 博客积分: 8003
  • 博客等级: 大校
  • 技术积分: 4275
  • 用 户 组: 普通用户
  • 注册时间: 2010-10-30 18:28
文章分类

全部博文(291)

文章存档

2017年(1)

2013年(47)

2012年(115)

2011年(121)

2010年(7)

分类: Mysql/postgreSQL

2011-05-20 15:18:03

系统开发完毕后,需要完备测试系统页面的逻辑功能,一条条考虑各种情况往数据库里插数据太麻烦了,为此我写了一个存储过程批量造数据,例子如下,共有三张表wideip,pool,status表
1.其中一个wideip包含多个pool(一对多)
2.status表是一个字典表有初始数据,wideip,pool里都有status_id外键,对应于status表里的id。
  初始值为
  
  1. INSERT INTO `status` VALUES ('0', 'available');
  2. INSERT INTO `status` VALUES ('1', 'offline');
  3. INSERT INTO `status` VALUES ('2', 'unknown');
  4. 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记录
表结构代码:
  1. SET FOREIGN_KEY_CHECKS=0;
  2. -- ----------------------------
  3. -- Table structure for pool
  4. -- ----------------------------
  5. CREATE TABLE `pool` (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,
  7.   `name` varchar(255) NOT NULL,
  8.   `wideip_id` int(11) NOT NULL,
  9.   `enable` tinyint(4) NOT NULL,
  10.   `status_id` tinyint(4) NOT NULL,
  11.   PRIMARY KEY (`id`),
  12.   KEY `wideip_pool_fk` (`wideip_id`),
  13.   KEY `status_pool_fk` (`status_id`),
  14.   CONSTRAINT `status_pool_fk` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  15.   CONSTRAINT `wideip_pool_fk` FOREIGN KEY (`wideip_id`) REFERENCES `wideip` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  16. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

  17. -- ----------------------------
  18. -- Table structure for status
  19. -- ----------------------------
  20. CREATE TABLE `status` (
  21.   `id` tinyint(4) NOT NULL,
  22.   `name` varchar(255) NOT NULL,
  23.   PRIMARY KEY (`id`)
  24. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  25. -- ----------------------------
  26. -- Table structure for wideip
  27. -- ----------------------------
  28. CREATE TABLE `wideip` (
  29.   `id` int(11) NOT NULL AUTO_INCREMENT,
  30.   `name` varchar(255) NOT NULL,
  31.   `enable` tinyint(4) NOT NULL,
  32.   `status_id` tinyint(4) NOT NULL,
  33.   PRIMARY KEY (`id`),
  34.   KEY `status_id` (`status_id`),
  35.   CONSTRAINT `wideip_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`)
  36. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
  37. INSERT INTO `status` VALUES ('0', 'available');
  38. INSERT INTO `status` VALUES ('1', 'offline');
  39. INSERT INTO `status` VALUES ('2', 'unknown');
  40. INSERT INTO `status` VALUES ('3', 'unavilable');
造数据的存储过程
  1. /*清空所有数据数据*/
  2. drop procedure if exists clear_all_data;
  3. delimiter ;;
  4. create procedure clear_all_data()
  5. BEGIN
  6.      /*清空数据*/
  7.   delete from wideip;
  8.   /*让自增从1*/
  9.   alter table wideip auto_increment 1;
  10.   /*清空pool数据*/
  11.     delete from pool;
  12.     /*让pool自增从1*/
  13.     alter table pool auto_increment 1;
  14. END;;
  15. delimiter ;


  16. drop procedure if exists make_pool_data;
  17. /*造每个wideip对于的pool数据*/
  18. delimiter ;;
  19. create procedure make_pool_data(wideipId int)
  20. BEGIN
  21.   declare d_enable int default 0;
  22.   declare d_status_id int default 0;
  23.   declare isError int default 0;
  24.      declare Done int default 0;
  25.      declare i int default 0;
  26.   
  27.         /* 声明游标 */
  28.   DECLARE rs CURSOR FOR select id from status order by id;
  29.   
  30.     /* 异常处理 */
  31.   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
  32.   DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;
  33.   
  34.     REPEAT
  35.             /* 打开游标 */
  36.         OPEN rs;
  37.         REPEAT
  38.             /*取status_id*/
  39.             FETCH rs INTO d_status_id;
  40.             IF Done!=1 THEN
  41.                 select d_enable,d_status_id;
  42.                 set i=i+1;
  43.                 insert into pool(name,enable,status_id,wideip_id) values(concat("name",wideipId,"_",i),d_enable,d_status_id,wideipId);
  44.             END IF;
  45.         UNTIL d_status_id is NULL or Done=1 END REPEAT;
  46.         CLOSE rs;
  47.         set d_enable=d_enable+1;
  48.         set Done=0;
  49.     UNTIL d_enable >= 2 END REPEAT;
  50. END;;
  51. delimiter ;


  52. drop procedure if exists make_wideip_data;
  53. /*造wideip的数据并调用 make_pool_data */
  54. delimiter ;;
  55. create procedure make_wideip_data()
  56. BEGIN
  57.   declare d_enable int default 0;
  58.   declare d_status_id int default 0;
  59.   declare isError int default 0;
  60.      declare Done int default 0;
  61.      declare i int default 0;
  62.      declare newWideipId int default 0;
  63.   
  64.         /* 声明游标 */
  65.   DECLARE rs CURSOR FOR select id from status order by id;
  66.   
  67.     /* 异常处理 */
  68.   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
  69.   DECLARE CONTINUE HANDLER FOR 1146 SET isError=1;
  70.   
  71.     REPEAT
  72.             /* 打开游标 */
  73.         OPEN rs;
  74.         REPEAT
  75.             /*取status_id*/
  76.             FETCH rs INTO d_status_id;
  77.             IF Done!=1 THEN
  78.                 select d_enable,d_status_id;
  79.                 set i=i+1;
  80.                 insert into wideip(name,enable,status_id) values(concat("name",i),d_enable,d_status_id);
  81.                 set newWideipId=last_insert_id();
  82.              call make_pool_data(newWideipId);
  83.             END IF;
  84.         UNTIL d_status_id is NULL or Done=1 END REPEAT;
  85.         CLOSE rs;
  86.         set d_enable=d_enable+1;
  87.         set Done=0;
  88.     UNTIL d_enable >= 2 END REPEAT;
  89. END;;
  90. delimiter ;
end
阅读(3506) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~