· 7 years ago · Oct 20, 2018, 04:26 AM
1CREATE PROCEDURE refIntChk (iID int, out exist int)
2
3BEGIN
4 set exist = (select count(dist) from t13 where (dist = iID));
5
6END;
7
8CREATE PROCEDURE createTables1 ()
9
10BEGIN
11
12set @run_time = current_time();
13
14 CREATE TABLE t13
15 (
16 dist int(11) not null,
17 repno int(11) not null,
18 repname varchar(16) not null,
19
20 PRIMARY KEY (dist)
21
22 ) ENGINE=InnoDB;
23
24
25 CREATE TABLE t14
26 (
27 cust int(11) not null primary key auto_increment,
28 custname varchar(16) not null,
29 district int(11) not null,
30 revenue float not null,
31 FOREIGN KEY (district) REFERENCES t13 (dist)
32
33 ) ENGINE=InnoDB;
34
35
36 SET @pDist_number = 5;
37 SET @custamah = 200;
38
39 SET @i = 0;
40 REPEAT
41 INSERT INTO t13
42 (
43 dist,
44 repno,
45 repname
46 )
47 VALUES
48 (
49 @i,
50 @i,
51 concat('name', @i)
52
53 );
54
55 SET @j = 0;
56 REPEAT
57 INSERT INTO T14
58 (
59 custname,
60 district,
61 revenue
62 )
63 VALUES
64 (
65 concat('Lars', @j),
66 @i,
67 rand()
68 );
69
70 set @j = @j + 1;
71
72 UNTIL (@j >= @custamah)
73
74 END REPEAT;
75
76
77
78 set @i = @i + 1;
79
80 UNTIL (@i >= @pDist_number)
81
82 END REPEAT;
83
84select timediff(current_time(), @run_time);
85
86END
87
88//
89
90CALL createTables1 () //
91
92DROP TABLE IF EXISTS t14 //
93DROP TABLE IF EXISTS t13 //
94
95CREATE PROCEDURE createTables2 ()
96
97BEGIN
98
99set @run_time = current_time();
100
101 CREATE TABLE t13
102 (
103 dist int(11) not null,
104 repno int(11) not null,
105 repname varchar(16) not null,
106
107 PRIMARY KEY (dist)
108
109 ) ENGINE=myIsam;
110
111
112 CREATE TABLE t14
113 (
114 cust int(11) not null primary key auto_increment,
115 custname varchar(16) not null,
116 district int(11) not null,
117 revenue float not null,
118 FOREIGN KEY (district) REFERENCES t13 (dist)
119
120 ) ENGINE=myIsam;
121
122
123 SET @pDist_number = 5;
124 SET @custamah = 200;
125
126 SET @i = 0;
127 REPEAT
128 INSERT INTO t13
129 (
130 dist,
131 repno,
132 repname
133 )
134 VALUES
135 (
136 @i,
137 @i,
138 concat('name', @i)
139
140 );
141
142 SET @j = 0;
143 REPEAT
144-------------------------------------------------------------------------------------------------------
145 CALL refIntChk (@i, @output)
146
147 IF (@output == 1)
148 select "@output == 1"
149
150 END
151--------------------------------------------------------------------------------------------------------
152 INSERT INTO T14
153 (
154 custname,
155 district,
156 revenue
157 )
158 VALUES
159 (
160 concat('Lars', @j),
161 @i,
162 rand()
163 );
164
165
166
167 set @j = @j + 1;
168
169 UNTIL (@j >= @custamah)
170
171 END REPEAT;
172
173
174
175 set @i = @i + 1;
176
177 UNTIL (@i >= @pDist_number)
178
179 END REPEAT;
180
181select timediff(current_time(), @run_time);
182
183END
184
185//
186
187CALL createTables2 () //