C4xNTkru

1# FUNCTION
2DROP FUNCTION IF EXISTS MarkBestseller;
3 
4DELIMITER $$
5CREATE FUNCTION `MarkBestseller`(rentspermonth DECIMAL(5,2)) RETURNS BOOLEAN
6    DETERMINISTIC
7BEGIN
8	DECLARE result BOOLEAN DEFAULT false;
9    IF rentspermonth > 2.00 THEN
10		SET result = true;
11	END IF;
12    RETURN result;
13END$$
14DELIMITER ;
15 
16#CREATING NEW COLUMN
17ALTER TABLE BOOKS ADD BESTSELLER BOOLEAN ;
18 
19#PROCEDURE
20DROP PROCEDURE IF EXISTS UpdateBestsellers;
21DELIMITER $$
22CREATE PROCEDURE UpdateBestsellers()
23BEGIN
24    DECLARE RENTNUMBER, DAYS, ID_BOOK INT;
25    DECLARE FINISHED INT DEFAULT 0;
26    DECLARE RENTSPERMONTH DECIMAL(5,2);
27    DECLARE ALL_BOOKS CURSOR FOR SELECT BOOK_ID FROM BOOKS;
28    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;
29   
30    OPEN ALL_BOOKS;
31    WHILE(FINISHED = 0) DO
32        FETCH ALL_BOOKS INTO ID_BOOK;
33        IF (FINISHED = 0) THEN
34            SELECT COUNT(*) FROM RENTS
35                WHERE BOOK_ID = ID_BOOK
36                INTO RENTNUMBER;
37            SELECT DATEDIFF(MAX(RENT_DATE), MIN(RENT_DATE)) + 1 FROM RENTS
38                WHERE BOOK_ID = ID_BOOK
39                INTO DAYS;
40            SET RENTSPERMONTH = RENTNUMBER / DAYS * 30;
41            UPDATE BOOKS SET BESTSELLER = MarkBestseller(RENTSPERMONTH)
42                WHERE BOOK_ID = ID_BOOK;
43                COMMIT;
44        END IF;
45    END WHILE;
46    CLOSE ALL_BOOKS;
47END $$
48 
49DELIMITER ;