4CsDGqGc

1alter table BOOKS drop column BESTSELLER;
2alter table BOOKS add BESTSELLER boolean;
3
4drop procedure if exists UpdateBestsellers;
5drop function if exists BestsellerLevel;
6
7DELIMITER $$
8
9create function BestsellerLevel(book_id int) returns boolean deterministic
10begin
11	declare result boolean default false;
12	if(book_id>2) then
13		set result = true;
14	end if;
15    return result;
16end $$
17
18DELIMITER ;
19
20DELIMITER $$
21
22create procedure UpdateBestsellers()
23begin
24    declare BK_ID int;
25    declare BESTSELLERS int;
26    declare FINISHED int default 0;
27    declare ALL_RESULTS cursor for select BOOK_ID from BOOKS;
28    declare continue handler for not found set FINISHED = 1;
29    open ALL_RESULTS;
30    while(FINISHED = 0) do
31		fetch ALL_RESULTS into BK_ID;
32		if(FINISHED = 0) then
33			select COUNT(*) 
34			from RENTS 
35			where BOOK_ID = BK_ID
36			into BESTSELLERS;
37			update BOOKS set BESTSELLER = BestsellerLevel(BESTSELLERS)
38			where BOOK_ID = BK_ID;
39            commit;
40		end if;
41	end while;
42    close ALL_RESULTS;        
43end $$
44
45DELIMITER ;
46
47call UpdateBestsellers();