TK9t9kHj

1alter table BOOKS add BESTSELLER boolean;
2
3drop procedure if exists UpdateBestsellers;
4
5delimiter $$
6
7create procedure UpdateBestsellers()
8begin
9	declare book_rents, days, bk_id int;
10    declare bestseller_var boolean default 0;
11    declare rentspermonth decimal(5.2);
12    declare finished int default 0;
13    declare all_books cursor for select BOOK_ID from BOOKS;
14    declare continue handler for not found set finished = 1;
15	
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 book_rents;
23			select datediff(max(RENT_DATE), min(RENT_DATE)) + 1 from RENTS
24				where BOOK_ID = bk_id
25                into days;
26			set rentspermonth = (book_rents / days) * 30;
27            if rentspermonth >= 2 then
28				set bestseller_var = 1;
29			else
30				set bestseller_var = 0;
31			end if;
32			update BOOKS set BESTSELLER = bestseller_var
33				where BOOK_ID = bk_id;
34			commit;
35		end if;
36	end while;
37close all_books;
38
39end $$
40
41delimiter ;
42
43
44call UpdateBestsellers();
45
46select * from BOOKS;