Tsb7DKLZ

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