aP3cfKEv

1DROP procedure IF EXISTS UpdateVipLevels;
2
3ALTER TABLE books
4ADD COLUMN BESTSELLER  TINYINT(1);
5commit;
6
7DELIMITER $$
8
9Create procedure UpdateBestsellers()
10Begin
11	declare BOOKSREAD, Days, BK_ID INT;
12    declare BOOKSPERMONTH DECIMAL(5,2);
13    declare FINISHED INT default 0;
14    declare ALL_BOOKS cursor for select BOOK_ID from RENTS;
15    declare continue handler for not found set FINISHED = 1;
16    open ALL_BOOKS;
17    while (FINISHED = 0) DO
18		fetch ALL_BOOKS into BK_ID;
19        if (FINISHED = 0) then
20			SELECT COUNT(*) FROM RENTS
21			 WHERE BOOK_ID = BK_ID
22			  INTO BOOKSREAD;
23			SELECT DATEDIFF(MAX(RENT_DATE), MIN(RENT_DATE)) + 1 FROM RENTS
24			 WHERE READER_ID = BK_ID
25			  INTO DAYS;
26			SET BOOKSPERMONTH = BOOKSREAD / DAYS *30;  
27            if BOOKSPERMONTH >= 2 then
28				UPDATE books SET BESTSELLER  = b'1' where BOOK_ID = BK_ID;
29			else
30				UPDATE books set BESTSELLER = b'0' where BOOK_ID = BK_ID;
31			end if;	
32            commit;
33		end if;
34	end while;
35	close ALL_BOOKS;
36end $$
37      
38DELIMITER ;