Npr66Gsp

1ALTER TABLE BOOKS ADD BESTSELLER BOOLEAN;
2
3DROP PROCEDURE IF EXISTS UpdateBestsellers;
4
5DELIMITER $$
6
7CREATE PROCEDURE UpdateBestsellers()
8BEGIN
9	DECLARE TIMESRENTED, DAYS, BK_ID INT;
10    DECLARE RENTSPERMONTH DECIMAL(5,2);
11    DECLARE FINISHED INT DEFAULT 0;
12    DECLARE ALL_BOOKS CURSOR FOR SELECT BOOK_ID FROM BOOKS;
13    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
14    OPEN ALL_BOOKS;
15    WHILE (FINISHED = 0) DO
16		FETCH ALL_BOOKS INTO BK_ID;
17		IF (FINISHED = 0) THEN
18			SELECT DATEDIFF(MIN(RENT_DATE), MAX(RENT_DATE)) + 1 FROM RENTS
19            WHERE BOOK_ID = BK_ID
20            INTO DAYS;
21            SELECT COUNT(*) FROM RENTS
22            WHERE BOOK_ID = BK_ID
23            INTO TIMESRENTED;
24            SET RENTSPERMONTH = TIMESRENTED / DAYS * 30;
25            IF (RENTSPERMONTH > 2) THEN
26				UPDATE BOOKS SET BESTSELLER = TRUE
27                WHERE BOOK_ID = BK_ID;
28			ELSE
29				UPDATE BOOKS SET BESTSELLER = FALSE
30				WHERE BOOK_ID = BK_ID;
31			END IF;
32            COMMIT;
33		END IF;
34	END WHILE;
35    CLOSE ALL_BOOKS;
36END $$
37
38DELIMITER ;
39
40CALL UpdateBestsellers();