1TF4yQ0N

1DROP FUNCTION IF EXISTS Bestseller;
2
3DELIMITER $$
4
5CREATE FUNCTION Bestseller (booksrented INT) RETURNS BOOLEAN DETERMINISTIC
6BEGIN
7			DECLARE result BOOLEAN;
8            IF booksrented >2 THEN
9            SET result = TRUE;
10            ELSE 
11            SET result = FALSE;
12            END IF;
13            RETURN result;
14
15END $$
16
17DELIMITER ;
18------------------------------------------------------------------
19
20
21ALTER TABLE BOOKS ADD BESTSELLER BOOLEAN;
22
23------------------------------------------------------------------
24
25DROP PROCEDURE IF EXISTS UpdateBestsellers;
26
27DELIMITER $$
28
29CREATE PROCEDURE UpdateBestsellers()
30BEGIN
31			DECLARE BOOKSREAD, B_ID INT;
32            DECLARE FINISHED INT DEFAULT 0;
33            DECLARE ALL_BOOKS CURSOR FOR SELECT BOOK_ID FROM BOOKS;
34            DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
35            
36            OPEN ALL_BOOKS;
37            WHILE (FINISHED = 0) DO
38				FETCH ALL_BOOKS INTO B_ID;
39                IF(FINISHED = 0 ) THEN
40                
41                SELECT COUNT(*) FROM RENTS
42                WHERE BOOK_ID = B_ID
43                INTO BOOKSREAD;
44                
45                UPDATE BOOKS SET BESTSELLER = Bestseller(BOOKSREAD)
46                WHERE BOOK_ID = B_ID;
47                COMMIT;
48                END IF;
49                END WHILE;
50                CLOSE ALL_BOOKS;
51
52END $$
53
54DELIMITER ;
55
56CALL UpdateBestsellers();