U2puTrMn

1MySQL PREPARE and EXECUTE statements inside procedure
2CREATE TABLE IF NOT EXISTS set1_dev_slot
3(
4id                 MEDIUMINT UNSIGNED                      NOT NULL AUTO_INCREMENT,
5device             TINYINT UNSIGNED                        NOT NULL,
6dev_date           DATE                                    NOT NULL,
7dev_time           TIME                                    NOT NULL,
8status             ENUM('FREE','PENDING','RESERVED')       NOT NULL DEFAULT 'FREE',
9dev_type           ENUM('FIN','CHAL','NONE')               NOT NULL DEFAULT 'NONE',
10
11PRIMARY KEY (id),
12UNIQUE (device,dev_date,dev_time),
13
14CONSTRAINT fk_device FOREIGN KEY (device) REFERENCES device (id)
15);
16	
17delimiter //
18CREATE PROCEDURE populateSlots (IN currentDateTime DATETIME, IN periodInDays INT)
19BEGIN
20    DECLARE iterDateTime DATETIME DEFAULT currentDateTime;
21    DECLARE numDevices INT;
22    SELECT COUNT(*) INTO numDevices FROM set1_devices;
23
24    WHILE DATEDIFF(iterDateTime, currentDateTime) < periodInDays DO
25    BEGIN
26        DECLARE device_iter INT DEFAULT 1;
27
28        WHILE device_iter < numDevices + 1 DO
29            INSERT INTO set1_dev_slot (device,dev_date,dev_time) VALUES (device_iter, DATE(iterDateTime), TIME(iterDateTime));
30            SET device_iter = device_iter + 1;
31        END WHILE;
32
33        SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 1 HOUR);
34        IF TIME(iterDateTime) = "02:00:00" THEN
35            SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 14 HOUR);
36        END IF;
37    END;
38    END WHILE;
39
40    BEGIN
41        DECLARE device_iter INT DEFAULT 1;
42
43        WHILE device_iter < numDevices + 1 DO
44            INSERT INTO set1_dev_slot (device,dev_date,dev_time) VALUES (device_iter, DATE(iterDateTime), TIME(iterDateTime));
45            SET device_iter = device_iter + 1;
46        END WHILE;
47
48        SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 1 HOUR);
49        SET device_iter = 1;
50
51        WHILE device_iter < numDevices + 1 DO
52            INSERT INTO set1_dev_slot (device,dev_date,dev_time) VALUES (device_iter, DATE(iterDateTime), TIME(iterDateTime));
53            SET device_iter = device_iter + 1;
54        END WHILE;
55    END;
56
57END;//
58delimiter ;
59	
60delimiter //
61CREATE PROCEDURE populateSlots (IN currentDateTime DATETIME, IN periodInDays INT, IN arena VARCHAR(32))
62BEGIN
63DECLARE iterDateTime DATETIME DEFAULT currentDateTime;
64DECLARE numDevices INT DEFAULT 5;
65
66
67WHILE DATEDIFF(iterDateTime, currentDateTime) < periodInDays DO
68BEGIN
69    DECLARE device_iter INT DEFAULT 1;
70
71    WHILE device_iter < numDevices + 1 DO
72        SET @sql_text = concat('INSERT INTO ', arena, ' (device,dev_date,dev_time) VALUES (',device_iter,', DATE("',iterDateTime,'"), TIME("',iterDateTime,'");');
73        PREPARE stmt FROM @sql_text;
74        EXECUTE stmt;
75        DEALLOCATE PREPARE stmt;
76        SET device_iter = device_iter + 1;
77    END WHILE;
78
79    SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 1 HOUR);
80    IF TIME(iterDateTime) = "02:00:00" THEN
81        SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 14 HOUR);
82    END IF;
83END;
84END WHILE;
85
86BEGIN
87    DECLARE device_iter INT DEFAULT 1;
88
89    WHILE device_iter < numDevices + 1 DO
90        SET @sql_text = concat('INSERT INTO ', arena, ' (device,dev_date,dev_time) VALUES (',device_iter,', DATE("',iterDateTime,'"), TIME("',iterDateTime,'");');
91        PREPARE stmt FROM @sql_text;
92        EXECUTE stmt;
93        DEALLOCATE PREPARE stmt;
94        SET device_iter = device_iter + 1;
95    END WHILE;
96
97    SET iterDateTime = DATE_ADD(iterDateTime, INTERVAL 1 HOUR);
98    SET field_iter = 1;
99
100    WHILE device_iter < numDevices + 1 DO
101        SET @sql_text = concat('INSERT INTO ', arena, ' (device,dev_date,dev_time) VALUES (',device_iter,', DATE("',iterDateTime,'"), TIME("',iterDateTime,'");');
102        PREPARE stmt FROM @sql_text;
103        EXECUTE stmt;
104        DEALLOCATE PREPARE stmt;
105        SET device_iter = device_iter + 1;
106    END WHILE;
107END;
108
109END;//
110delimiter ;