ASIX/M10/UF1/A06/E1

ASIX M10 UF1

Pt61. Cursors

Guillem Solà i Boeck (host: PC05)

Enunciat

Solució

CREATE DEFINER=`root`@`localhost` PROCEDURE `ex_cursors`()
BEGIN
	DECLARE finalLL INT DEFAULT FALSE;
	DECLARE llengua VARCHAR(200);
    DECLARE parlantsLL INT;
    DECLARE cLlengues CURSOR FOR
		SELECT DISTINCT Language FROM CountryLanguage ORDER BY Language;
	    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finalLL = TRUE;
    
    OPEN cLlengues;
    bucleLL:LOOP
		FETCH cLlengues INTO llengua;
			IF finalLL THEN LEAVE bucleLL; END IF;
            
            SELECT SUM(CountryLanguage.Percentage/100*Country.Population) AS Parlants from CountryLanguage JOIN Country ON CountryLanguage.CountryCode=Country.Code WHERE Language = llengua INTO parlantsLL;
            SELECT CONCAT(llengua, ' -- ', parlantsLL) AS 'Llengua -- parlants';
			
        blocConti:BEGIN
			DECLARE finalConti INT DEFAULT FALSE;
			DECLARE continent VARCHAR(200);
            DECLARE parlantsConti INT;
			DECLARE cContinent CURSOR FOR
				SELECT Country.Continent FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode=Country.Code WHERE CountryLanguage.Language=llengua ORDER BY Country.Continent;
	    	
		   DECLARE CONTINUE HANDLER FOR NOT FOUND SET finalConti = TRUE;
            
            OPEN cContinent;
            bucleConti:LOOP
				FETCH cContinent INTO continent;
					IF finalConti THEN LEAVE bucleConti; END IF;
                
                SELECT SUM(CountryLanguage.Percentage/100*Country.Population) FROM CountryLanguage JOIN Country ON CountryLanguage.CountryCode=Country.Code WHERE Language = llengua AND Country.Continent = continent INTO parlantsConti;

                SELECT CONCAT('   ',continent, ' -- ', parlantsConti) AS 'Continent -- Parlants';
                
                blocPais:BEGIN
					DECLARE finalPais INT DEFAULT FALSE;
					DECLARE pais VARCHAR(200);
                    DECLARE parlantsPais INT;
					DECLARE cPais CURSOR FOR
						SELECT Country.Name FROM Country JOIN CountryLanguage ON CountryLanguage.CountryCode=Country.Code 
                        WHERE CountryLanguage.Language=llengua AND Country.Continent=continent 
                        ORDER BY Country.Name;
					DECLARE CONTINUE HANDLER FOR NOT FOUND SET finalPais = TRUE;

					OPEN cPais;
		          buclePais:LOOP
						FETCH cPais INTO pais;
							IF finalPais THEN LEAVE buclePais; END IF;
						
                        
                        SELECT SUM(CountryLanguage.Percentage/100*Country.Population) FROM CountryLanguage JOIN Country ON CountryLanguage.CountryCode=Country.Code WHERE Language = llengua AND Country.Continent = continent AND Country.Name = pais INTO parlantsPais;
                        SELECT concat('      ',pais, ' -- ', parlantsPais) AS 'Pais -- Parlants';
                        
                        ITERATE buclePais;
                    END LOOP buclePais;
            
                END blocPais;
                
                ITERATE bucleConti;
            END LOOP bucleConti;
        END blocConti;
                   
		ITERATE bucleLL;
	END LOOP bucleLL;
    CLOSE cLlengues;
END

Execució

MariaDB [world]> call ex_cursors;