7pZ2iNNf

1set @currentTime = CURTIME();
2
3DROP PROCEDURE IF EXISTS refIntChk//
4CREATE PROCEDURE refIntChk(distID INT, out existing INT ) 
5BEGIN 
6	SET existing = (SELECT distID from t13 where (dist=distID));
7END//
8
9
10DROP PROCEDURE IF EXISTS tprocedure//
11CREATE PROCEDURE tprocedure()
12BEGIN
13	DROP TABLE IF EXISTS t14;
14	DROP TABLE IF EXISTS t13;
15	CREATE TABLE t13 (
16		dist int(11) not null primary key,
17		repno int(11) not null,
18		repname varchar(16) not null
19	) engine=myIsam;
20
21	CREATE TABLE t14 (
22		cust int(11) not null primary key auto_increment,
23		custname varchar(16) not null,
24		district int(11) not null,
25		revenue float not null
26	) engine=myIsam;
27
28	/* Variables */
29	SET @loop_current = 0;
30	SET @loop_end = 5;
31
32	WHILE @loop_current < @loop_end do 
33		CALL refIntChk(@loop_current, @existing);
34		IF @existing IS null then
35			
36			set @sub_loop_current = 0;
37			set @sub_loop_end = 10000;
38			
39			WHILE @sub_loop_current < @sub_loop_end do
40				INSERT INTO t14 
41					(custname, district, revenue)
42				VALUES 
43					(@sub_loop_current, @sub_loop_current, rand()); 
44				set @sub_loop_current = @sub_loop_current + 1;
45			END WHILE; 
46
47			INSERT INTO t13 
48				(dist, repno, repname)
49			VALUES 
50				(@loop_current, @loop_current, concat('name', @loop_current));
51		ELSE
52			select "error";
53		END IF;
54		set @loop_current = @loop_current + 1;
55	END WHILE; 
56END //
57
58CALL tprocedure() //
59
60SELECT TIMEDIFF(CURTIME(), @currentTime);