8wi2ZxwT

1#ALTER TABLE BOOKS ADD BESTSELLER BOOLEAN NOT NULL ;
2
3
4DROP PROCEDURE IF EXISTS UpdateBestsellers;
5DELIMITER $$
6
7CREATE PROCEDURE UpdateBestsellers()
8BEGIN
9	DECLARE FINISHED INT DEFAULT 0;
10    DECLARE RBK_ID, BBK_ID, DATEDIF INT;
11    DECLARE RTDATE DATETIME;
12	DECLARE ALL_BOOKS CURSOR FOR SELECT B.BOOK_ID, R.RENT_DATE, DATEDIFF(MAX(R.RENT_DATE), MIN(R.RENT_DATE)) + 1 AS D FROM BOOKS B, RENTS R  WHERE B.BOOK_ID = R.BOOK_ID;
13    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
14    OPEN ALL_BOOKS;
15    WHILE (FINISHED = 0) DO
16		FETCH ALL_BOOKS INTO BBK_ID, RTDATE, DATEDIF;
17        IF FINISHED = 0 THEN
18        UPDATE BOOKS SET BESTSELLER = TRUE
19        WHERE BOOK_ID > 0 AND COUNT(DATEDIF) > 1;
20        ELSE
21        UPDATE BOOKS SET BESTSELLER = FALSE
22        WHERE COUNT(DATEDIF) < 2;
23        
24        COMMIT;
25        END IF;
26	END WHILE;
27    
28END $$
29
30DELIMITER ;