· 7 years ago · Oct 13, 2018, 02:38 AM
1DELIMITER //
2
3DROP TABLE IF EXISTS t4 //
4DROP TABLE IF EXISTS t3 //
5
6SELECT "CREATE TABLE" //
7
8CREATE TABLE t3(
9 dist int(11) not null,
10 repno int(11) not null,
11 repname varchar(16) not null,
12 PRIMARY KEY(dist)
13 ) ENGINE=innodb; //
14
15
16CREATE TABLE t4(
17 cust int(11) not null auto_increment,
18 custname varchar(16) not null,
19 district int(11) not null,
20 revenue float not null,
21 PRIMARY KEY(cust),
22 FOREIGN KEY(district) REFERENCES t3 (dist)
23 ) ENGINE=innodb; //
24
25DROP PROCEDURE IF EXISTS district5 //
26
27CREATE PROCEDURE district5 ()
28 BEGIN
29 DECLARE d INT;
30 DECLARE c INT;
31 SET d = 0;
32 WHILE d < 5 DO
33 INSERT INTO t3 (
34 dist,
35 repno,
36 repname
37 )
38 VALUES (
39 d,
40 d,
41 'John'
42 );
43 SET c = 0;
44 WHILE c < 10000 DO
45 INSERT INTO t4 (
46 custname,
47 district,
48 revenue
49 )
50 VALUES (
51 c,
52 d,
53 rand()*100);
54 SET c = c+1;
55 END WHILE;
56 SET d = d+1;
57 END WHILE;
58END; //
59
60CALL district5(); //