VNR6V1i0

1drop procedure if exists UpdateBestsellers; 
2
3alter table books add bestseller boolean;
4
5delimiter $$
6
7create procedure UpdateBestsellers()
8begin
9declare numer_rents int;
10declare rent_quantity int;
11declare finished int default 0;
12declare ALL_BOOKS cursor for select book_id from books;
13declare continue handler for not found set finished = 1;
14open ALL_BOOKS;
15while(finished=0) do
16fetch ALL_BOOKS into numer_rents;
17if (finished = 0) then
18select count(*) from rents
19where BOOK_ID = numer_rents
20into rent_quantity;
21if(rent_quantity>=2) then
22update books set bestseller = true;
23elseif (rent_quantity<2) then
24update books set bestseller = false;
25commit;
26end if;
27end if;
28end while;
29close ALL_BOOKS;
30end $$
31
32delimiter ;