gTACgYfp

1CREATE PROCEDURE getRoomInfo()
2BEGIN
3DECLARE id1 INTEGER DEFAULT 0;
4DECLARE id2 INTEGER  DEFAULT 0;
5DECLARE id3 INTEGER  DEFAULT 0;
6DECLARE price1 decimal(6,2) DEFAULT 0;
7DECLARE price2 decimal(6,2) DEFAULT 0;
8DECLARE price3 decimal(6,2) DEFAULT 0;
9DECLARE done INTEGER DEFAULT 0;
10DECLARE hotel_id INTEGER DEFAULT 0;
11
12DECLARE cur_hotel CURSOR  FOR SELECT hoteId FROM booking_rooms GROUP BY hoteId;
13
14DECLARE cur1 CURSOR  FOR SELECT id, price FROM booking_rooms WHERE totalAdults = 1 AND hoteId=hotel_id ORDER BY price;
15DECLARE cur2 CURSOR  FOR SELECT id, price FROM booking_rooms WHERE totalAdults = 2 AND hoteId=hotel_id AND totalChildren=1 ORDER BY price;
16DECLARE cur3 CURSOR  FOR SELECT id, price FROM booking_rooms WHERE totalAdults = 3 AND hoteId=hotel_id ORDER BY price;
17
18
19DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
20DROP TEMPORARY TABLE IF EXISTS combinations;
21CREATE TEMPORARY TABLE combinations (hotelId INT, id VARCHAR(100), price DECIMAL(10,2)) ENGINE = Memory;
22
23OPEN cur_hotel;
24hotel_loop: LOOP
25    FETCH cur_hotel INTO hotel_id;
26    IF done=1 THEN
27        LEAVE hotel_loop;
28    END IF;
29    OPEN cur1;    OPEN cur2;    OPEN cur3;
30    cycle_loop: LOOP
31        FETCH cur1 INTO id1, price1;
32        FETCH cur2 INTO id2, price2;
33        FETCH cur3 INTO id3, price3;
34        IF done=1 THEN
35            set done=0;
36            LEAVE cycle_loop;
37        END IF;
38
39        INSERT INTO combinations(hotelId, id,price) 
40            VALUES (hotel_id, CONCAT_WS(',',id1,id2,id3),price1+price2+price3);
41     END LOOP cycle_loop;
42    CLOSE cur1;
43   CLOSE cur2;
44  CLOSE cur3;
45 END LOOP hotel_loop;
46CLOSE cur_hotel;
47
48SELECT * FROM combinations;
49DROP TEMPORARY TABLE combinations;
50END`