4.CREATE PROCEDURE `pro3`(num INT)
5.BEGIN
6.DECLARE fname CHAR(1);
7.DECLARE name1 CHAR(1);
8.DECLARE name2 CHAR(1);
9.DECLARE fullname VARCHAR(3);
10.DECLARE pre_fullname VARCHAR(3) DEFAULT \'\';
11.DECLARE age INT;
12.DECLARE i INT DEFAULT 1;
13.WHILE i <=num DO
14. SET fname = SUBSTRING(\'赵钱孙李周吴郑王\',FLOOR(1+8*RAND()),1);
15. SET name1 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1);
16. SET name2 = SUBSTRING(\'一二三四五六七八九十甲乙丙丁\',ROUND(1+14*RAND()),1);
17.
18. IF ROUND(RAND())=0 THEN
19. SET fullname = CONCAT(fname,name1);
20. END IF;
21. IF ROUND(RAND())=1 THEN
22. SET fullname = CONCAT(fname,name1,name2);
23. END IF;
24.
25. SET age = FLOOR(20+31*RAND());
26. /* 给临时变量赋值，看最新的一条记录的name值 */
27. SELECT NAME INTO pre_fullname FROM user1 ORDER BY id DESC LIMIT 1;
28. /* 开始判断，如果刚赋值的name跟上一次赋值的name值一样，就跳过，i保持不变，继续取值；否则i+1，继续循环取值*/
29. IF fullname=pre_fullname AND i>1 THEN
30. SET i=i;
31. ELSEIF fullname IS NULL THEN
32. SET i=i;
33. ELSE
34. INSERT INTO user1 VALUES(NULL,fullname,age);
35. SET i = i + 1;
36. END IF ;
37.END WHILE;
38.END$$
39.DELIMITER