1AKLhX7x

1DROP PROCEDURE create_tables;
2DELIMITER $$
3CREATE PROCEDURE create_tables ( )
4BEGIN
5  DECLARE counter BIGINT DEFAULT 0;
6
7  DROP TABLE IF EXISTS `user_test`;
8  CREATE TABLE `user_test` (
9	`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
10	`gender` enum('MALE' , 'FEMALE') NOT NULL,
11	`first_name` varchar(50) NOT NULL, 
12	`last_name` varchar(50) NOT NULL, 
13	`date_of_birth` date,
14	`active`  boolean DEFAULT true,
15	`create_datetime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
16	`create_user` varchar(32) NOT NULL ,
17	`update_count` int(11) DEFAULT NULL DEFAULT 0,
18	`update_user` varchar(32) NOT NULL ,
19	`sys_update_datetime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
20	PRIMARY KEY (`user_id`)
21  ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;
22  
23  
24END$$
25DELIMITER ;
26
27        
28-- begin
29
30DROP PROCEDURE populate_table;
31DELIMITER $$
32CREATE PROCEDURE populate_table ( in noOfUsers int(11) )
33BEGIN
34  DECLARE counter BIGINT DEFAULT 0;
35
36  loop_out: LOOP
37    SET counter=counter+1;
38
39    IF counter > noOfUsers THEN
40      LEAVE loop_out;
41    END IF;
42
43  		INSERT INTO user_test
44			(first_name, last_name, gender, date_of_birth, create_user, update_user)  
45		values 
46		( 	
47			SUBSTRING( CAST( md5(rand()) AS CHAR), 1, 3), 
48			SUBSTRING( CAST( md5(rand()) AS CHAR), 1, 4) , 
49			elt(rand() * 2 + 0.5, 'MALE', 'FEMALE'), 
50			concat( floor(rand() * 70 ) + 1950, '-', floor(rand() * 11 ) + 1 , '-' , floor(rand() * 25 ) + 1)
51			, 'testuser', 'testuser'
52		) ;
53        
54  END LOOP loop_out;
55END$$
56DELIMITER ;