7mjget0k

1use mydb;
2SET GLOBAL log_bin_trust_function_creators = 1;
3
4-- Найти самую долгую серию побед
5drop table if exists `mydb`.`TeamSeason`;
6create table if not exists `mydb`.`TeamSeason` (
7	`gameDate` int not null auto_increment,
8    `rival` varchar(45),
9    `result` int,
10    primary key (`gameDate`, `rival`, `result`)
11);
12
13insert into TeamSeason (`rival`, `result`) value ("Barselona", 1);
14insert into TeamSeason (`rival`, `result`) value ("Spartak", 0);
15insert into TeamSeason (`rival`, `result`) value ("CSKA", 1);
16insert into TeamSeason (`rival`, `result`) value ("Zenit", 1);
17insert into TeamSeason (`rival`, `result`) value ("Italy", 0);
18insert into TeamSeason (`rival`, `result`) value ("Milan", -1);
19insert into TeamSeason (`rival`, `result`) value ("Roma", 1);
20insert into TeamSeason (`rival`, `result`) value ("Ajax", 1);
21insert into TeamSeason (`rival`, `result`) value ("Dinamo", 1);
22insert into TeamSeason (`rival`, `result`) value ("Lyonnais", 0);
23insert into TeamSeason (`rival`, `result`) value ("Dinamo", 1);
24insert into TeamSeason (`rival`, `result`) value ("Ural", 1);
25
26drop function if exists `mydb`.`calcMaxWinSeries`;
27DELIMITER //
28CREATE FUNCTION `mydb`.`calcMaxWinSeries`()
29RETURNS INTEGER
30BEGIN
31	declare maxSeries int;
32    declare currentSeries int;
33    
34    declare gameDate int;
35    declare rival int;
36    declare result int;
37    
38    declare done integer default 0;
39    
40    declare seriesCursor Cursor for select gameDate, rival, result from `mydb`.`TeamSeason`;
41    
42    declare continue handler for sqlstate '02000' SET done=1;
43    
44    open seriesCursor;
45    set currentSeries = 0;
46    set maxSeries = 0;
47    while done = 0 do
48		fetch seriesCursor into gameDate, rival, result;
49        if(result = 1) then
50			set currentSeries = currentSeries + 1;
51		end if;
52        
53        if(currentSeries > maxSeries) then
54			set maxSeries = currentSeries;
55		end if;
56            
57		if(result <> 1) then
58			set currentSeries = 0;
59		end if;
60	end while;
61    close seriesCursor;
62    return maxSeries;
63END//
64DELIMITER ;
65
66select calcMaxWinSeries();